Last week we started a new contest, Puzzle of the Week. Today we publish correct answers for the 1st puzzle:
|
Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat). |
Solution #1: No Sub-query solution! We consider it the best solution.
To better understand the following query we suggest you to first check if you can understand Solution #3 (see below).
SELECT MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'1', LEVEL)) SUN,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'2', LEVEL)) MON,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'3', LEVEL)) TUE,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'4', LEVEL)) WED,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'5', LEVEL)) THU,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'6', LEVEL)) FRI,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'7', LEVEL)) SAT
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY')
ORDER BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY');
Solution #2: Using PIVOT
SELECT "'SUN'" SU,"'MON'" MO,"'TUE'" TU,"'WED'" WE,
"'THU'" TH,"'FRI'" FR,"'SAT'" SA
FROM
(
SELECT TRUNC(TRUNC(SYSDATE,'MON')+LEVEL-1,'DAY') WEEK_START,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DD') DD,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DY') DY
FROM DUAL
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'yyyymm')=
TO_CHAR(SYSDATE,'yyyymm')
)
PIVOT
(
MAX(DD)
FOR DY IN ('SUN','MON','TUE','WED','THU','FRI','SAT')
)
ORDER BY week_start;
Solution #3: Use the power of CONNECT BY clause to generate a range of days for the current month
WITH x AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+level-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')
Solution #4: Use existing table(s) to generate a range of days for the current month
WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+ROWNUM-1 D
FROM emp,emp
WHERE TO_CHAR(TRUNC(SYSDATE, 'MON')+ROWNUM-1, 'YYYYMM')=TO_CHAR(SYSDATE, 'YYYYMM')
AND ROWNUM<=31
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE ' ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')
Solution #5: Present each calendar week as a single column value – using LISTAGG function
WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
), y AS (
SELECT LISTAGG(TO_CHAR(d,'DD'), ' ') WITHIN GROUP(ORDER BY d) AS week, TRUNC(D, 'DAY') wday
FROM X
GROUP BY TRUNC(D, 'DAY')
)
SELECT CASE WHEN week LIKE '01%' THEN LPAD(week, 26)
ELSE week
END AS "SUN MON TUE WED THU FRI SAT"
FROM y
ORDER BY wday
Solution #6: Present each calendar week as a single column value – using SYS_CONNECT_BY_PATH function
WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT CASE WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
LPAD(MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')), 21)
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
START WITH TO_CHAR(d,'DD')='01' OR d=TRUNC(d,'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1
Solution #7: A variation of Solution #6
SELECT CASE
WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
LPAD(MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')), 21)
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM (SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1) x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
START WITH TO_CHAR(d,'DD')='01' OR d=TRUNC(d,'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.