NEXT_DAY関数、TRUNC関数を使って、向こう1000日分のプレミアムフライデーを求めてみる。(Oracle Database)

これでワイもプレミアムや！彡(ﾟ)(ﾟ)

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
 SELECT TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM')      AS TUKI
      , MAX(TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'DD')) AS PREMIUM
   FROM DUAL
CONNECT BY LEVEL <= 1000
  GROUP BY TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM')
  ORDER BY TUKI;

TUKI       PREMIUM
---------- ----------
2017/03/01 2017/03/31
2017/04/01 2017/04/28
2017/05/01 2017/05/26
2017/06/01 2017/06/30
2017/07/01 2017/07/28
2017/08/01 2017/08/25
2017/09/01 2017/09/29
2017/10/01 2017/10/27
2017/11/01 2017/11/24
2017/12/01 2017/12/29
2018/01/01 2018/01/26

TUKI       PREMIUM
---------- ----------
2018/02/01 2018/02/23
2018/03/01 2018/03/30
2018/04/01 2018/04/27
2018/05/01 2018/05/25
2018/06/01 2018/06/29
2018/07/01 2018/07/27
2018/08/01 2018/08/31
2018/09/01 2018/09/28
2018/10/01 2018/10/26
2018/11/01 2018/11/30
2018/12/01 2018/12/28

TUKI       PREMIUM
---------- ----------
2019/01/01 2019/01/25
2019/02/01 2019/02/22
2019/03/01 2019/03/29
2019/04/01 2019/04/26
2019/05/01 2019/05/31
2019/06/01 2019/06/28
2019/07/01 2019/07/26
2019/08/01 2019/08/30
2019/09/01 2019/09/27
2019/10/01 2019/10/25
2019/11/01 2019/11/29

33 rows selected.

※再帰WITH句の書き方も追記したやで彡(ﾟ)(ﾟ)

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
WITH rec(lv) AS (
  SELECT 1 FROM DUAL
   UNION ALL
  SELECT lv + 1
    FROM REC
   WHERE lv < 1000
)
SELECT TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM')      AS TUKI
     , MAX(TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'DD')) AS PREMIUM
  FROM rec
 GROUP BY TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM')
 ORDER BY TUKI;

TUKI       PREMIUM
---------- ----------
2017/03/01 2017/03/31
2017/04/01 2017/04/28
2017/05/01 2017/05/26
2017/06/01 2017/06/30
2017/07/01 2017/07/28
2017/08/01 2017/08/25
2017/09/01 2017/09/29
2017/10/01 2017/10/27
2017/11/01 2017/11/24
2017/12/01 2017/12/29
2018/01/01 2018/01/26

TUKI       PREMIUM
---------- ----------
2018/02/01 2018/02/23
2018/03/01 2018/03/30
2018/04/01 2018/04/27
2018/05/01 2018/05/25
2018/06/01 2018/06/29
2018/07/01 2018/07/27
2018/08/01 2018/08/31
2018/09/01 2018/09/28
2018/10/01 2018/10/26
2018/11/01 2018/11/30
2018/12/01 2018/12/28

TUKI       PREMIUM
---------- ----------
2019/01/01 2019/01/25
2019/02/01 2019/02/22
2019/03/01 2019/03/29
2019/04/01 2019/04/26
2019/05/01 2019/05/31
2019/06/01 2019/06/28
2019/07/01 2019/07/26
2019/08/01 2019/08/30
2019/09/01 2019/09/27
2019/10/01 2019/10/25
2019/11/01 2019/11/29

33 rows selected.