SQLize Online / PHPize Online  /  SQLtest Online

/*with my_date as (select trunc(to_date('30.12.2024', 'dd.mm.yyyy')) md from dual), matr AS (SELECT * FROM (SELECT LEVEL w FROM dual CONNECT BY LEVEL <= 6) r CROSS JOIN (SELECT 1 d1, 2 d2, 3 d3, 4 d4, 5 d5, 6 d6, 7 d7 FROM dual) s) SELECT m.w, NVL(d.val, m.d1) d1, NVL(d.val, m.d2) d2, NVL(d.val, m.d3) d3, m.d4, m.d5, m.d6, NVL(d.val, m.d7) d7 FROM matr m LEFT OUTER JOIN (SELECT d.val, d.dt, trunc((d.dt - trunc(trunc(d.dt, 'MM'), 'IW')) / 7) + 1 w, d.dt - TRUNC(d.dt, 'IW') + 1 d FROM (SELECT LEVEL val, TRUNC(d.md, 'MM') + LEVEL - 1 dt FROM my_date d CONNECT BY LEVEl <= LAST_DAY(d.md) - TRUNC(d.md, 'MM') + 1) d) d ON d.w = m.w AND d.d = DECODE(d.d, 1, m.d1, 2, m.d2, 3, m.d3, 4, m.d4, 5, m.d5, 6, m.d6, 7, m.d7);*/ ROW_NUMBER() OVER(PARTITION BY MON ORDER BY NEW_YWEEK) AS MWEEK, SUM(DECODE(WDAY, '1', MDAY, NULL)) AS MON, SUM(DECODE(WDAY, '2', MDAY, NULL)) AS TUE, SUM(DECODE(WDAY, '3', MDAY, NULL)) AS WED, SUM(DECODE(WDAY, '4', MDAY, NULL)) AS THU, SUM(DECODE(WDAY, '5', MDAY, NULL)) AS FRI, SUM(DECODE(WDAY, '6', MDAY, NULL)) AS SAT, SUM(DECODE(WDAY, '7', MDAY, NULL)) AS SUN FROM (SELECT DAYOFYEAR AS EVERYDAY, TO_CHAR(DAYOFYEAR, 'mm') AS MON, TO_CHAR(DAYOFYEAR, 'Month') AS MON_NAME, TO_CHAR(DAYOFYEAR, 'w') AS MWEEK, TO_CHAR(DAYOFYEAR, 'ww') AS YWEEK, CASE WHEN (TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd') > '1') AND (TO_CHAR(DAYOFYEAR, 'd') < TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd')) THEN TO_CHAR(TO_CHAR(DAYOFYEAR, 'ww') + 1, 'fm00') ELSE TO_CHAR(DAYOFYEAR, 'ww') END AS NEW_YWEEK, DECODE(MOD(TO_CHAR(DAYOFYEAR, 'd')-1, 7), 0,7, TO_CHAR(DAYOFYEAR, 'd')-1) AS WDAY, /*decode( TO_CHAR(DAYOFYEAR, 'd') , '2','1','3','2','4','3','5','4' ,'6','5','7','6' ,'7' )AS WDAY,*/ TO_CHAR(DAYOFYEAR, 'dd') AS MDAY FROM (SELECT TO_DATE(&YEAR || '0101', 'yyyymmdd') + LEVEL -1 AS DAYOFYEAR FROM DUAL CONNECT BY LEVEL <= TO_CHAR(TO_DATE(&YEAR || '1231', 'yyyymmdd'), 'ddd')) --where TO_CHAR(DAYOFYEAR, 'Month') = 'November' ) GROUP BY MON, MON_NAME, NEW_YWEEK;