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:
Level: Advanced

jigsaw-puzzle-piece 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):

p01-2

or this:

p01-2

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.

 

Advertisements

7 thoughts on “Puzzle of the Week Challenge

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

    here is the query

    with cal as (
    select trunc(Last_Day(ADD_MONTHS(sysdate,-1))+1) ym from dual)
    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,
    MAX(DECODE(TO_CHAR(dt,’d’),’1′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sun”,
    MAX(DECODE(TO_CHAR(dt,’d’),’2′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Mon”,
    MAX(DECODE(TO_CHAR(dt,’d’),’3′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Tue”,
    MAX(DECODE(TO_CHAR(dt,’d’),’4′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Wed”,
    MAX(DECODE(TO_CHAR(dt,’d’),’5′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Thu”,
    MAX(DECODE(TO_CHAR(dt,’d’),’6′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Fri”,
    MAX(DECODE(TO_CHAR(dt,’d’),’7′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sat”
    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,
      MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
      MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
      MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
      MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
      MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
      MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
      MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
      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'), TRUNC( dt, 'day' ))
      ORDER BY TO_DATE( MONTH, 'Month YYYY' ),week
      

      TO_CHAR( dt+1, ‘iw’ ) is changed to TRUNC(dt,’day’)

      Good job!

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