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.

Advertisements

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
    connect by add_months(trunc(sysdate,’YYYY’),0)+level-1<=add_months(trunc(sysdate,'YYYY')-1,12)
    )
    )
    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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s