## Puzzle of the Week Challenge

Today we are starting a new challenge – every week we will publish a puzzle (various levels).

There will be some awards and signs of recognition to the winners.

Puzzle of the week:

 Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat).

Expected result may look like this (in SQL*Plus):

or this:

A correct answer (and workarounds!) will be published here in a week.

### 8 thoughts on “Puzzle of the Week Challenge”

1. sunitha February 19, 2016 / 11:04 pm

here is the query

with cal as (
SELECT * FROM (
SELECT MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘1’, LEVEL)) SUN
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘2’, LEVEL)) MON
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘3’, LEVEL)) TUE
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘4’, LEVEL)) WED
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘5’, LEVEL)) THU
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘6’, LEVEL)) FRI
, MIN (DECODE (TO_CHAR (YM + LEVEL – 1, ‘d’), ‘7’, LEVEL)) SAT
FROM cal
CONNECT BY LEVEL <= LAST_DAY (YM) – YM + 1
GROUP BY TRUNC (YM + LEVEL, 'iw')
ORDER BY 7)

2. Marius Ivanuta February 20, 2016 / 7:28 am

with d as (
SELECT
TO_CHAR(TRUNC(to_date(‘&&1′,’dd/mm/yyyy’), ‘MM’) + level – 1,’DD’) AS no,
TO_CHAR(TRUNC(to_date(‘&&1′,’dd/mm/yyyy’), ‘MM’) + level -1,’D’) AS day_week,
TO_CHAR(TRUNC(to_date(‘&&1′,’dd/mm/yyyy’), ‘MM’) + level- 1,’IW’) AS week
FROM
dual
CONNECT BY level <= to_number(TO_CHAR(last_day(to_date('&&1','dd/mm/yyyy')), 'DD')) order by week, day_week
)
select
trim(xmlagg(xmlelement(test, decode(day_week,1,no)|| ' ')).extract('//text()')) Mo,
trim(xmlagg(xmlelement(test, decode(day_week,2,no)|| ' ')).extract('//text()')) Tu,
trim(xmlagg(xmlelement(test, decode(day_week,3,no)|| ' ')).extract('//text()')) We,
trim(xmlagg(xmlelement(test, decode(day_week,4,no)|| ' ')).extract('//text()')) Th,
trim(xmlagg(xmlelement(test, decode(day_week,5,no)|| ' ')).extract('//text()')) Fr,
trim(xmlagg(xmlelement(test, decode(day_week,6,no)|| ' ')).extract('//text()')) Su,
trim(xmlagg(xmlelement(test, decode(day_week,7,no)|| ' ')).extract('//text()')) Sa
from d GROUP by week order by week;

• Zahar Hilkevich February 26, 2016 / 8:11 am

Almost there.
For running the query in SQL*Plus we will need to do some pre-formatting:

col Mo for a2
col Tu for a2
col We for a2
col Th for a2
col Fr for a2
col Sa for a2
col Su for a2

The result for the Feb-2016 (on a US server) looks like this:

```MO TU WE TH FR SU SA
-- -- -- -- -- -- --
07 01 02 03 04 05 06
14 08 09 10 11 12 13
21 15 16 17 18 19 20
28 22 23 24 25 26 27
29
```

Monday and Tuesdays are a bit off, but the main idea is correct!

3. sajith February 24, 2016 / 10:55 am

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,”Sun”, “Mon”, “Tue”,
“Wed”, “Thu”, “Fri”, “Sat”
FROM (SELECT TO_CHAR(dt,’fmMonthfm YYYY’) MONTH,TO_CHAR(dt+1,’iw’) week,
FROM ( SELECT TRUNC(SYSDATE,’y’)-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

• Zahar Hilkevich February 26, 2016 / 8:19 am

Only one (current month) was actually needed and that part of your query would be correct.

One little flaw: your query puts the week order incorrectly in January:

```MONTH                Sun      Mon      Tue      Wed      Thu      Fri      Sat
-------------------- -------- -------- -------- -------- -------- -------- ------
January 2016      3        4        5        6        7        8        9
January 2016     10       11       12       13       14       15       16
January 2016     17       18       19       20       21       22       23
January 2016     24       25       26       27       28       29       30
January 2016     31
January 2016                                                   1        2
February 2016               1        2        3        4        5        6
February 2016      7        8        9       10       11       12       13
February 2016     14       15       16       17       18       19       20
February 2016     21       22       23       24       25       26       27
February 2016     28       29
```

I have corrected your query a bit:

```SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TRUNC(dt,'day') week,