SQL代码：关于yyymm的案例语句的问题，回顾了12个月。然后标记0如果有日期或其他1

发布于 2025-01-20 10:46:04 字数 890 浏览 0 评论 0原文

早上好。我正在使用蟾蜍。我编写了这段代码，它按预期工作。我希望将 201801 之后的 Min YYYYMM 标记为 1 ，其他所有内容标记为 0 。

201712 MARKET 0
201801 MARKET 1
201802 MARKET 0
201905 MARKET 0

。

WITH

T1 AS (SELECT DISTINCT STORE STORE
, MIN(YYYYMM) MIN_YYYYMM


FROM STORE_ORDERS

WHERE YYYYMM >= '201801'
AND FRUIT = 'APPLE'

GROUP BY STORE 
)

SELECT DISTINCT T2.YYYY YYYY
, T2.YYYYMM YYYYMM
, T2.STORE STORE
, T2.FRUIT FRUIT
, CASE WHEN T1.STORE = T2.STORE 
    AND T1.MIN_YYYYMM = T2.YYYYMM
    THEN 1 ELSE 0 END FIRST_ORDER

FROM STORE_ORDERS T2

INNER JOIN T1
ON T2.STORE = T1.STORE

WHERE T2.FRUIT = 'APPLE'

ORDER BY T2.STORE
, T2.YYYYMM ASC

我的问题在于第二次询问代码。我需要查看“1”日期并查看该日期之前 12 个月内是否有日期。如果有那么它应该是 0 如果没有它应该是“1”（如下例所示）

201712 MARKET 0
201801 MARKET 0
201802 MARKET 0
201905 MARKET 1

原文

Good Morning. I am using TOAD. I wrote this code and it works as intended. I wanted the Min YYYYMM after 201801 to be tagged 1 and everything else 0.

201712 MARKET 0
201801 MARKET 1
201802 MARKET 0
201905 MARKET 0

WITH

T1 AS (SELECT DISTINCT STORE STORE
, MIN(YYYYMM) MIN_YYYYMM


FROM STORE_ORDERS

WHERE YYYYMM >= '201801'
AND FRUIT = 'APPLE'

GROUP BY STORE 
)

SELECT DISTINCT T2.YYYY YYYY
, T2.YYYYMM YYYYMM
, T2.STORE STORE
, T2.FRUIT FRUIT
, CASE WHEN T1.STORE = T2.STORE 
    AND T1.MIN_YYYYMM = T2.YYYYMM
    THEN 1 ELSE 0 END FIRST_ORDER

FROM STORE_ORDERS T2

INNER JOIN T1
ON T2.STORE = T1.STORE

WHERE T2.FRUIT = 'APPLE'

ORDER BY T2.STORE
, T2.YYYYMM ASC

My question lies in the second ask for the code. I need to look at that '1' date and see if there was a date within 12 months before that date. If there was then it should be a 0 if there wasn't it should be a '1' (like the example below)