SELECT CASE
WHEN (new_yweek =
MIN (new_yweek) OVER (PARTITION BY mon ORDER BY new_yweek)
)
THEN mon_name
ELSE NULL
END AS MONTH,
new_yweek AS yweek,
ROW_NUMBER () OVER (PARTITION BY mon ORDER BY new_yweek) AS mweek,
SUM (DECODE (wday, '1', mday, NULL)) AS sun,
SUM (DECODE (wday, '2', mday, NULL)) AS mon,
SUM (DECODE (wday, '3', mday, NULL)) AS tue,
SUM (DECODE (wday, '4', mday, NULL)) AS wed,
SUM (DECODE (wday, '5', mday, NULL)) AS thu,
SUM (DECODE (wday, '6', mday, NULL)) AS fri,
SUM (DECODE (wday, '7', mday, NULL)) AS sat
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,
TO_CHAR (dayofyear, 'd') 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'
)))
GROUP BY mon, mon_name, new_yweek;
