## Puzzle of the week #3

Calendar Summary Report:

Write a single SELECT statement that outputs number of Sundays, Mondays, Tuesdays, etc in each month of the current year.

The output should look like this:

```MONTH  SUN  MON  TUE  WED  THU  FRI  SAT
----- ---- ---- ---- ---- ---- ---- ----
JAN      5    4    4    4    4    5    5
FEB      4    5    4    4    4    4    4
MAR      4    4    5    5    5    4    4
APR      4    4    4    4    4    5    5
MAY      5    5    5    4    4    4    4
JUN      4    4    4    5    5    4    4
JUL      5    4    4    4    4    5    5
AUG      4    5    5    5    4    4    4
SEP      4    4    4    4    5    5    4
OCT      5    5    4    4    4    4    5
NOV      4    4    5    5    4    4    4
DEC      4    4    4    4    5    5    5

```

To submit your answer (one or more!) please start following this blog and add a comment to this post.

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

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.

### 5 thoughts on “Puzzle of the week #3”

1. Chetan Narvekar March 7, 2016 / 1:03 am

select Month,mon,tue,wed,thu,fri,sat,sun from
(
select Month,mnth1,dy,count(1) as cnt
from
(
select to_char(datee,’MON’) as Month,to_char(datee,’MM’) mnth1,to_char(datee,’DY’) dy
from
(
select add_months(trunc(sysdate,’YYYY’),0)+level-1 as datee
from dual
)
)
group by Month,mnth1,dy
)
pivot (max(cnt) for dy in ('MON' mon,'TUE' tue,'WED' wed,'THU' thu,'FRI' fri,'SAT' sat,'SUN' sun)) order by mnth1

2. Deepak Mahto March 8, 2016 / 9:30 am

please find my attempt!

WITH data1 AS
(SELECT TO_CHAR(TRUNC(to_date(’01-JAN-2016′,’DD-MON-YYYY’)) + level -1,’MONTH’) Mon,
TO_CHAR(TRUNC(to_date(’01-JAN-2016′,’DD-MON-YYYY’)) + level -1,’DY’) dy
FROM dual
CONNECT BY level <= (last_day(to_date('31-DEC-2016','DD-MON-YYYY')) – TRUNC(to_date('01-JAN-2016','DD-MON-YYYY'))) + 1
)
SELECT *
FROM
(SELECT mon as Month, dy, COUNT(*) cnt FROM data1 GROUP BY mon, dy
) pivot (SUM(cnt) AS DAY FOR (DY) IN ('SUN' as SUN,'MON' as MON,'TUE' as TUE,'WED' as WED,'THU' as THU,'FRI' as FRI,'SAT' as SAT))
ORDER BY DECODE(trim(Month),'JANUARY',1,'FEBRUARY',2,'MARCH',3,'APRIL',4,'MAY',5,'JUNE',6,'JULY',7,'AUGUST',8,'SEPTEMBER',9,'OCTOBER',10,'NOVEMBER',11,'DECEMBER',12)

3. Deepak Mahto March 8, 2016 / 11:56 am

WITH data1 AS
(SELECT TO_CHAR(TRUNC(to_date(’01-JAN-2016′,’DD-MON-YYYY’)) + level -1,’MONTH’) Mon,
TO_CHAR(TRUNC(to_date(’01-JAN-2016′,’DD-MON-YYYY’)) + level -1,’DY’) dy
FROM dual
CONNECT BY level <= (last_day(to_date('31-DEC-2016','DD-MON-YYYY')) – TRUNC(to_date('01-JAN-2016','DD-MON-YYYY'))) + 1
)
SELECT *
FROM
(SELECT mon as Month, dy, COUNT(*) cnt FROM data1 GROUP BY mon, dy
) pivot (SUM(cnt) AS DAY FOR (DY) IN ('SUN' as SUN,'MON' as MON,'TUE' as TUE,'WED' as WED,'THU' as THU,'FRI' as FRI,'SAT' as SAT))
ORDER BY DECODE(trim(Month),'JANUARY',1,'FEBRUARY',2,'MARCH',3,'APRIL',4,'MAY',5,'JUNE',6,'JULY',7,'AUGUST',8,'SEPTEMBER',9,'OCTOBER',10,'NOVEMBER',11,'DECEMBER',12)