Home » Other » General » Puzzle n°07 - Create A Calendar for the Given Month and Year *  () 1 Vote
Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #290970] Wed, 02 January 2008 07:11 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This might be Old and 'Outdated' version of Puzzle but might be enough to rise some sort of curiosity.

This puzles is about to create a calendar representation of given month in given year in rows and columns like


SQL> /

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


In case Input is '01' for month and '2008' for year .

Have a nice Luck !!!

[Please add this to sticky Puzzle , if it is found interesting ]

Thumbs Up
Rajuvan.

[Edit MC: change Z^001 to number and add complexity rate, please Rajuvan do it yourself next time]


[Updated on: Wed, 02 January 2008 08:57] by Moderator

Report message to a moderator

Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #297166 is a reply to message #290970] Wed, 30 January 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58958
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As there are many ways to do it and already published in PL/SQL, I will provide a new one fully in SQL.
SQL> Def month=11
SQL> Def year=1956
SQL> alter session set nls_territory=AMERICA nls_date_language=AMERICAN;
SQL> Set feed off
SQL> Set head off
SQL> Def month=11
SQL> Def year=1956
SQL> Col line format a50
SQL> Col nop noprint
SQL> With 
  2    -- days: 1 line per week day
  3    days as ( select level day from dual connect by level <= 7 ),
  4    -- weeks: 1 line per possible week in a month
  5    weeks as ( select level-1 week from dual connect by level <= 6 ),
  6    -- mdays: each day of the month within each week
  7    mdays as ( 
  8      select week, weekday,
  9             case 
 10               when day > to_char(last_day(to_date('&Month/&Year','MM/YYYY')),'DD')
 11                 then '   '
 12               when day <= 0 then '   '
 13               else to_char(day,'99')
 14             end monthday
 15      from ( select week, day weekday,
 16                    7*week+day-to_char(to_date('&Month/&Year','MM/YYYY'),'D')+1 day
 17             from weeks, days
 18           )
 19    )
 20  -- Display blank line
 21  select 0 nop, null line from dual
 22  union all
 23  -- Display Month title
 24  select 1 nop,
 25         to_char(to_date('&month/&year','MM/YYYY'),' FMMonth YYYY') line
 26  from dual
 27  union all
 28  -- Display blank line
 29  select 2 nop, null line from dual
 30  union all
 31  -- Display week day name
 32  select 3 nop, 
 33         sys_connect_by_path(substr(to_char(trunc(sysdate,'D')+day-1,'Day'),
 34                                    1,3),' ') line
 35  from days
 36  where day = 7
 37  connect by prior day = day-1
 38  start with day = 1
 39  union all
 40  -- Display each week
 41  select 4+week nop, replace(sys_connect_by_path(monthday,'/'), '/', ' ') line
 42  from mdays
 43  where weekday = 7
 44  connect by prior week = week and prior weekday = weekday-1
 45  start with weekday = 1
 46  order by 1
 47  /

 November 1956

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

What is interesting is that whatever is your country, the query always displays the calendar with the first day of week for you in the first column (Lundi is Monday in French):
SQL> alter session set nls_territory=FRANCE nls_date_language=FRENCH;
47  /

Novembre 1956

Lun Mar Mer Jeu Ven Sam Dim
              1   2   3   4
  5   6   7   8   9  10  11
 12  13  14  15  16  17  18
 19  20  21  22  23  24  25
 26  27  28  29  30

SQL> alter session set nls_territory=IRAQ nls_date_language=AMERICAN;
 47  /

 November 1956

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

Regards
Michel
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #346505 is a reply to message #290970] Mon, 08 September 2008 14:24 Go to previous messageGo to next message
Brian Tkatch
Messages: 6
Registered: September 2008
Location: Oak Park, MI USA
Junior Member

I thought i would try it myself. This is not as international as Michael's, but it is much simpler.

Year and Month is put in the WITH:

WITH
	Data
AS
	(
	 SELECT 
		TO_DATE('200811', 'YYYYMM') YM
	 FROM
		Dual
	)
SELECT
	CASE WHEN D + 1 BETWEEN 1 AND LD THEN TO_CHAR(D + 1, 'FM09') ELSE '--' END SU,
	CASE WHEN D + 2 BETWEEN 1 AND LD THEN TO_CHAR(D + 2, 'FM09') ELSE '--' END MO,
	CASE WHEN D + 3 BETWEEN 1 AND LD THEN TO_CHAR(D + 3, 'FM09') ELSE '--' END TU,
	CASE WHEN D + 4 BETWEEN 1 AND LD THEN TO_CHAR(D + 4, 'FM09') ELSE '--' END WE,
	CASE WHEN D + 5 BETWEEN 1 AND LD THEN TO_CHAR(D + 5, 'FM09') ELSE '--' END TH,
	CASE WHEN D + 6 BETWEEN 1 AND LD THEN TO_CHAR(D + 6, 'FM09') ELSE '--' END FR,
	CASE WHEN D + 7 BETWEEN 1 AND LD THEN TO_CHAR(D + 7, 'FM09') ELSE '--' END SA
FROM
	(
	 SELECT
		EXTRACT(DAY FROM YM) - TO_CHAR(YM, 'D') + ((Level - 1) * 7)	D,
		EXTRACT(DAY FROM LAST_DAY(YM))					LD
	 FROM
		Data
	 CONNECT BY
		Level < = Level <= CEIL((EXTRACT(DAY FROM LAST_DAY(YM)) + TO_CHAR(YM, 'D') - 1) / 7)
	);

[Updated on: Mon, 08 September 2008 15:46]

Report message to a moderator

Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #347397 is a reply to message #346505] Thu, 11 September 2008 08:29 Go to previous messageGo to next message
Brian Tkatch
Messages: 6
Registered: September 2008
Location: Oak Park, MI USA
Junior Member

I just realized i pasted that incorrectly. I don;t see an edit button, so i will post it again without the extra "LEVEL < =":

WITH
	Data
AS
	(
	 SELECT 
		TO_DATE('200811', 'YYYYMM') YM
	 FROM
		Dual
	)
SELECT
	CASE WHEN D + 1 BETWEEN 1 AND LD THEN TO_CHAR(D + 1, 'FM09') ELSE '--' END SU,
	CASE WHEN D + 2 BETWEEN 1 AND LD THEN TO_CHAR(D + 2, 'FM09') ELSE '--' END MO,
	CASE WHEN D + 3 BETWEEN 1 AND LD THEN TO_CHAR(D + 3, 'FM09') ELSE '--' END TU,
	CASE WHEN D + 4 BETWEEN 1 AND LD THEN TO_CHAR(D + 4, 'FM09') ELSE '--' END WE,
	CASE WHEN D + 5 BETWEEN 1 AND LD THEN TO_CHAR(D + 5, 'FM09') ELSE '--' END TH,
	CASE WHEN D + 6 BETWEEN 1 AND LD THEN TO_CHAR(D + 6, 'FM09') ELSE '--' END FR,
	CASE WHEN D + 7 BETWEEN 1 AND LD THEN TO_CHAR(D + 7, 'FM09') ELSE '--' END SA
FROM
	(
	 SELECT
		EXTRACT(DAY FROM YM) - TO_CHAR(YM, 'D') + ((Level - 1) * 7)	D,
		EXTRACT(DAY FROM LAST_DAY(YM))					LD
	 FROM
		Data
	 CONNECT BY
		Level <= CEIL((EXTRACT(DAY FROM LAST_DAY(YM)) + TO_CHAR(YM, 'D') - 1) / 7)
	);
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #349741 is a reply to message #290970] Mon, 22 September 2008 23:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nice One Brian Tkatch Cool Cool Cool

Thumbs Up
Rajuvan.
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360601 is a reply to message #347397] Fri, 21 November 2008 10:20 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

another simple and not international one:

with t as (
select to_date('200811','yyyymm') 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 T
CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1
  GROUP BY TRUNC (YM + LEVEL, 'iw'))
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360606 is a reply to message #290970] Fri, 21 November 2008 10:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Wow .. Another Nice One zozo.. Hats Off !!!

Its interesting to watch the reducing complexity level from Michel's Post to Zozo's Post.

Smile
Rajuvan.
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360609 is a reply to message #360601] Fri, 21 November 2008 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58958
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with t as (
  2  select to_date('200811','yyyymm') ym from dual)
  3  SELECT * FROM (
  4   SELECT MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '1', LEVEL)) SUN
  5        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '2', LEVEL)) MON
  6        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '3', LEVEL)) TUE
  7        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '4', LEVEL)) WED
  8        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '5', LEVEL)) THU
  9        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '6', LEVEL)) FRI
 10        , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '7', LEVEL)) SAT
 11     FROM T
 12  CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1
 13    GROUP BY TRUNC (YM + LEVEL, 'iw'))
 14  /
       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
         9         10         11         12         13         14         15
        23         24         25         26         27         28         29
         2          3          4          5          6          7          8
                                                                           1
        16         17         18         19         20         21         22
        30

6 rows selected.

Maybe an ORDER BY is missing.

Regards
Michel
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360652 is a reply to message #360609] Fri, 21 November 2008 17:59 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

yes if you use >= 10g
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360653 is a reply to message #360652] Fri, 21 November 2008 18:01 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

this is the last one:

with t as (
select to_date('200211','yyyymm') 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 T
CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1
  GROUP BY TRUNC (YM + LEVEL, 'iw')
  ORDER BY 7)

Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360704 is a reply to message #290970] Sat, 22 November 2008 16:16 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
A model version is here:
http://volder-notes.blogspot.com/2008/01/current-month-calendar-model-clause.html

Gints Plivna

[Updated on: Sat, 22 November 2008 23:52] by Moderator

Report message to a moderator

Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #360773 is a reply to message #290970] Mon, 24 November 2008 00:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Good.. Nice and rare gintsp . Good search Smile

Let me add One more Alternative but in PL/SQL. Though outdated , I hope it will add the value to the thread.

I got it form Oracle link


create or replace procedure calendar(month varchar,year varchar) as type weeks
is varray(6) of varchar2(21); 
l_weeks weeks := weeks(); 
l_last_day number;
l_w_day number; 
begin l_weeks.extend(6); 
l_w_day := to_number(to_char(to_date(month||year,'MMYYYY'),'D'));
l_last_day := to_number(to_char(last_day(to_date(month||year,'MMYYYY')),'DD'));
for i in 1..l_last_day loop
l_weeks(trunc((i+l_w_day-2)/7)+1) := l_weeks(trunc((i+l_w_day-2)/7)+1)||' '||rpad(to_char(i),2); 
end loop;
dbms_output.put_line('----- '||month||'-'||year||' -----'); 
dbms_output.put_line('--------------------');
dbms_output.put_line('Su Mo Tu We Th Fr Sa'); 
dbms_output.put_line(lpad(l_weeks(1),20,'-'));
for i in 2..6 loop
dbms_output.put_line(l_weeks(i));
end loop;
dbms_output.put_line('--------------------');
end;

Smile
Rajuvan
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #570193 is a reply to message #360773] Tue, 06 November 2012 11:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2010
Registered: January 2010
Senior Member
Just for fun:

SET PAGESIZE 0
SET LINESIZE 132
WITH t1 AS (
            SELECT  TRUNC(SYSDATE,'YYYY') yyyy,
                    3 vertical_offset,
                    5 horizontal_offset,
                    9 lines_per_month,
                    TO_NUMBER(TO_CHAR(SYSDATE,'MM')) curr_mm,
                    2 months_per_row
              FROM  DUAL
           ),
     t3 AS (
            SELECT  '*'||LPAD('['||TO_CHAR(ADD_MONTHS(yyyy,LEVEL - 1),'FMMonth') || ']',27,'-')||'--*' l,
                    CASE
                      WHEN LEVEL <= CEIL(curr_mm / months_per_row) * months_per_row
                        THEN lines_per_month * (LEVEL - 1) + 1
                        ELSE lines_per_month * (LEVEL - 1) + lines_per_month
                    END w,
                    LEVEL mm
              FROM  t1
              CONNECT BY LEVEL < 13
           ),
     t4 AS (
            SELECT  l,
                    lines_per_month * (LEVEL - 1) + 2 w,
                    LEVEL mm
              FROM  (
                     SELECT  '|' || REPLACE(SYS_CONNECT_BY_PATH(LPAD(TO_CHAR(NEXT_DAY(yyyy,LEVEL),'Dy'),4),','),',') || ' |' l,
                             lines_per_month
                       FROM  t1
                       WHERE LEVEL = 7
                       CONNECT BY LEVEL < 8
                    )
              CONNECT BY LEVEL < 13
           ),
     t5 AS (
            SELECT  LPAD(' ',(ADD_MONTHS(yyyy,LEVEL-1)-NEXT_DAY(ADD_MONTHS(yyyy,LEVEL-1)-7,1))*4)||d m,
                    LEVEL mm,
                    lines_per_month
              FROM  t1,
                    (
                     SELECT  REPLACE(SYS_CONNECT_BY_PATH(TO_CHAR(LEVEL,'999'),','),',') || LPAD(' ',56) d
                       FROM  DUAL
                       WHERE LEVEL = 31
                       CONNECT BY LEVEL < 32
                    )
              CONNECT BY LEVEL < 13
           ),
     t6 AS (
            SELECT  '|' || SUBSTR(m,(LEVEL - 1) * 28 + 1, 28) || ' |' l,
                    lines_per_month * (mm - 1) + 2 + LEVEL w,
                    mm
              FROM  t5
              CONNECT BY PRIOR mm = mm
                     AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
                     AND LEVEL < lines_per_month - 2
           ),
     t7 AS (
            SELECT  '*' || LPAD('-',29,'-') || '*' l,
                    CASE
                      WHEN LEVEL <= ROUND(curr_mm / months_per_row) * months_per_row
                        THEN lines_per_month * (LEVEL - 1) + lines_per_month
                        ELSE lines_per_month * (LEVEL - 1) + 1
                    END w,
                    LEVEL mm
              FROM  t1
              CONNECT BY LEVEL < 13
           ),
     t8 as (
            SELECT  l,
                    w,
                    mm,
                    vertical_offset,
                    horizontal_offset,
                    curr_mm,
                    lines_per_month,
                    months_per_row,
                    MOD(w - 1,lines_per_month) oo,
                    TRUNC((w - 1) / (months_per_row * lines_per_month)) ww,
                    MOD(w - 1,months_per_row * lines_per_month) ss,
                    CEIL(mm / months_per_row) rn
              FROM  t1,
                    (
                      SELECT  *
                        FROM  t3
                     UNION ALL
                      SELECT  *
                      FROM  t4
                     UNION ALL
                      SELECT  *
                        FROM  t6
                     UNION ALL
                      SELECT  *
                        FROM  t7
                    )
           )
SELECT  LPAD(' ',(rn - LEVEL) * horizontal_offset) ||
        REPLACE(SYS_CONNECT_BY_PATH(CASE
                                      WHEN rn = CEIL(curr_mm / months_per_row)
                                        THEN l
                                      WHEN rn < CEIL(curr_mm / months_per_row)
                                        THEN CASE CONNECT_BY_ISLEAF
                                               WHEN 1
                                                 THEN l
                                                 ELSE SUBSTR(l,1,horizontal_offset)
                                             END
                                         ELSE CASE LEVEL
                                               WHEN 1
                                                 THEN l
                                                 ELSE SUBSTR(l,-horizontal_offset)
                                             END
                                    END,
                                    ','
                                   ),
                 ',') l
  FROM  (
         SELECT  REPLACE(SYS_CONNECT_BY_PATH(l,','),',') l,
                 mm,
                 w,
                 curr_mm,
                 vertical_offset,
                 horizontal_offset,
                 months_per_row,
                 rn,
                 (rn - 1) * vertical_offset + MOD(ROWNUM - 1,lines_per_month) + 1 o,
                 CASE WHEN rn > 1 THEN ROWNUM - rn * lines_per_month + vertical_offset ELSE 1 END s
           FROM  t8
           WHERE LEVEL = months_per_row
           START WITH ss < lines_per_month
           CONNECT BY ww = PRIOR ww
                  AND oo = PRIOR oo
                  AND ss > PRIOR ss
        )
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH s > 0 OR vertical_offset = 0
  CONNECT BY vertical_offset > 0
         AND o = PRIOR o
         AND rn = PRIOR rn + 1
  ORDER BY w
/
*------------------[January]--**-----------------[February]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 ||               1   2   3   4 |
|   8*--------------------[March]--**--------------------[April]--*
|  15| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|  22|                   1   2   3 ||   1   2   3   4   5   6   7 |
|  29|   4*----------------------[May]--**---------------------[June]--*
|    |  11| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
*----|  18|           1   2   3   4   5 ||                       1   2 |
     |  25|   6*---------------------[July]--**-------------------[August]--*
     |    |  13| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
     *----|  20|   1   2   3   4   5   6   7 ||               1   2   3   4 |
          |  27|   8*----------------[September]--**------------------[October]--*
          |    |  15| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
          *----|  22|                           1 ||       1   2   3   4   5   6 |
               |  29|   2*-----------------[November]--**-----------------[December]--*
               |    |   9| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
               *----|  16|                   1   2   3 ||                           1 |
                    |  23|   4   5   6   7   8   9  10 ||   2   3   4   5   6   7   8 |
                    |  30|  11  12  13  14  15  16  17 ||   9  10  11  12  13  14  15 |
                    *----|  18  19  20  21  22  23  24 ||  16  17  18  19  20  21  22 |
                         |  25  26  27  28  29  30  31 ||  23  24  25  26  27  28  29 |
                         |                             ||  30  31                     |
                         *-----------------------------**-----------------------------*

24 rows selected.

SQL> 


And by changing vertical_offset, horizontal_offset, lines_per_month, months_per_row:

WITH t1 AS (
            SELECT  TRUNC(SYSDATE,'YYYY') yyyy,
                    0 vertical_offset,
                    0 horizontal_offset,
                    9 lines_per_month,
                    TO_NUMBER(TO_CHAR(SYSDATE,'MM')) curr_mm,
                    1 months_per_row
              FROM  DUAL
           ),

*------------------[January]--*
| Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 |
|   8   9  10  11  12  13  14 |
|  15  16  17  18  19  20  21 |
|  22  23  24  25  26  27  28 |
|  29  30  31                 |
|                             |
*-----------------------------*
*-----------------[February]--*
| Sun Mon Tue Wed Thu Fri Sat |
|               1   2   3   4 |
|   5   6   7   8   9  10  11 |
|  12  13  14  15  16  17  18 |
|  19  20  21  22  23  24  25 |
|  26  27  28  29  30  31     |
|                             |
*-----------------------------*
*--------------------[March]--*
| Sun Mon Tue Wed Thu Fri Sat |
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28  29  30  31 |
|                             |
*-----------------------------*
*--------------------[April]--*
| Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 |
|   8   9  10  11  12  13  14 |
|  15  16  17  18  19  20  21 |
|  22  23  24  25  26  27  28 |
|  29  30  31                 |
|                             |
*-----------------------------*
*----------------------[May]--*
| Sun Mon Tue Wed Thu Fri Sat |
|           1   2   3   4   5 |
|   6   7   8   9  10  11  12 |
|  13  14  15  16  17  18  19 |
|  20  21  22  23  24  25  26 |
|  27  28  29  30  31         |
|                             |
*-----------------------------*
*---------------------[June]--*
| Sun Mon Tue Wed Thu Fri Sat |
|                       1   2 |
|   3   4   5   6   7   8   9 |
|  10  11  12  13  14  15  16 |
|  17  18  19  20  21  22  23 |
|  24  25  26  27  28  29  30 |
|  31                         |
*-----------------------------*
*---------------------[July]--*
| Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 |
|   8   9  10  11  12  13  14 |
|  15  16  17  18  19  20  21 |
|  22  23  24  25  26  27  28 |
|  29  30  31                 |
|                             |
*-----------------------------*
*-------------------[August]--*
| Sun Mon Tue Wed Thu Fri Sat |
|               1   2   3   4 |
|   5   6   7   8   9  10  11 |
|  12  13  14  15  16  17  18 |
|  19  20  21  22  23  24  25 |
|  26  27  28  29  30  31     |
|                             |
*-----------------------------*
*----------------[September]--*
| Sun Mon Tue Wed Thu Fri Sat |
|                           1 |
|   2   3   4   5   6   7   8 |
|   9  10  11  12  13  14  15 |
|  16  17  18  19  20  21  22 |
|  23  24  25  26  27  28  29 |
|  30  31                     |
*-----------------------------*
*------------------[October]--*
| Sun Mon Tue Wed Thu Fri Sat |
|       1   2   3   4   5   6 |
|   7   8   9  10  11  12  13 |
|  14  15  16  17  18  19  20 |
|  21  22  23  24  25  26  27 |
|  28  29  30  31             |
|                             |
*-----------------------------*
*-----------------[November]--*
| Sun Mon Tue Wed Thu Fri Sat |
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28  29  30  31 |
|                             |
*-----------------------------*
*-----------------------------*
| Sun Mon Tue Wed Thu Fri Sat |
|                           1 |
|   2   3   4   5   6   7   8 |
|   9  10  11  12  13  14  15 |
|  16  17  18  19  20  21  22 |
|  23  24  25  26  27  28  29 |
|  30  31                     |
*-----------------[December]--*

108 rows selected.

 
WITH t1 AS (
            SELECT  TRUNC(SYSDATE,'YYYY') yyyy,
                    0 vertical_offset,
                    0 horizontal_offset,
                    9 lines_per_month,
                    TO_NUMBER(TO_CHAR(SYSDATE,'MM')) curr_mm,
                    3 months_per_row
              FROM  DUAL
           ),
*------------------[January]--**-----------------[February]--**--------------------[March]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 ||               1   2   3   4 ||                   1   2   3 |
|   8   9  10  11  12  13  14 ||   5   6   7   8   9  10  11 ||   4   5   6   7   8   9  10 |
|  15  16  17  18  19  20  21 ||  12  13  14  15  16  17  18 ||  11  12  13  14  15  16  17 |
|  22  23  24  25  26  27  28 ||  19  20  21  22  23  24  25 ||  18  19  20  21  22  23  24 |
|  29  30  31                 ||  26  27  28  29  30  31     ||  25  26  27  28  29  30  31 |
|                             ||                             ||                             |
*-----------------------------**-----------------------------**-----------------------------*
*--------------------[April]--**----------------------[May]--**---------------------[June]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 ||           1   2   3   4   5 ||                       1   2 |
|   8   9  10  11  12  13  14 ||   6   7   8   9  10  11  12 ||   3   4   5   6   7   8   9 |
|  15  16  17  18  19  20  21 ||  13  14  15  16  17  18  19 ||  10  11  12  13  14  15  16 |
|  22  23  24  25  26  27  28 ||  20  21  22  23  24  25  26 ||  17  18  19  20  21  22  23 |
|  29  30  31                 ||  27  28  29  30  31         ||  24  25  26  27  28  29  30 |
|                             ||                             ||  31                         |
*-----------------------------**-----------------------------**-----------------------------*
*---------------------[July]--**-------------------[August]--**----------------[September]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 ||               1   2   3   4 ||                           1 |
|   8   9  10  11  12  13  14 ||   5   6   7   8   9  10  11 ||   2   3   4   5   6   7   8 |
|  15  16  17  18  19  20  21 ||  12  13  14  15  16  17  18 ||   9  10  11  12  13  14  15 |
|  22  23  24  25  26  27  28 ||  19  20  21  22  23  24  25 ||  16  17  18  19  20  21  22 |
|  29  30  31                 ||  26  27  28  29  30  31     ||  23  24  25  26  27  28  29 |
|                             ||                             ||  30  31                     |
*-----------------------------**-----------------------------**-----------------------------*
*------------------[October]--**-----------------[November]--**-----------------[December]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|       1   2   3   4   5   6 ||                   1   2   3 ||                           1 |
|   7   8   9  10  11  12  13 ||   4   5   6   7   8   9  10 ||   2   3   4   5   6   7   8 |
|  14  15  16  17  18  19  20 ||  11  12  13  14  15  16  17 ||   9  10  11  12  13  14  15 |
|  21  22  23  24  25  26  27 ||  18  19  20  21  22  23  24 ||  16  17  18  19  20  21  22 |
|  28  29  30  31             ||  25  26  27  28  29  30  31 ||  23  24  25  26  27  28  29 |
|                             ||                             ||  30  31                     |
*-----------------------------**-----------------------------**-----------------------------*

36 rows selected.

WITH t1 AS (
            SELECT  TRUNC(SYSDATE,'YYYY') yyyy,
                    3 vertical_offset,
                    5 horizontal_offset,
                    9 lines_per_month,
                    TO_NUMBER(TO_CHAR(SYSDATE,'MM')) curr_mm,
                    2 months_per_row
              FROM  DUAL
           ),
*------------------[January]--**-----------------[February]--*
| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|   1   2   3   4   5   6   7 ||               1   2   3   4 |
|   8*--------------------[March]--**--------------------[April]--*
|  15| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
|  22|                   1   2   3 ||   1   2   3   4   5   6   7 |
|  29|   4*----------------------[May]--**---------------------[June]--*
|    |  11| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
*----|  18|           1   2   3   4   5 ||                       1   2 |
     |  25|   6*---------------------[July]--**-------------------[August]--*
     |    |  13| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
     *----|  20|   1   2   3   4   5   6   7 ||               1   2   3   4 |
          |  27|   8*----------------[September]--**------------------[October]--*
          |    |  15| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
          *----|  22|                           1 ||       1   2   3   4   5   6 |
               |  29|   2*-----------------[November]--**-----------------[December]--*
               |    |   9| Sun Mon Tue Wed Thu Fri Sat || Sun Mon Tue Wed Thu Fri Sat |
               *----|  16|                   1   2   3 ||                           1 |
                    |  23|   4   5   6   7   8   9  10 ||   2   3   4   5   6   7   8 |
                    |  30|  11  12  13  14  15  16  17 ||   9  10  11  12  13  14  15 |
                    *----|  18  19  20  21  22  23  24 ||  16  17  18  19  20  21  22 |
                         |  25  26  27  28  29  30  31 ||  23  24  25  26  27  28  29 |
                         |                             ||  30  31                     |
                         *-----------------------------**-----------------------------*

24 rows selected.



SY.
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #570194 is a reply to message #570193] Tue, 06 November 2012 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58958
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator



./fa/5263/0/



Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #570199 is a reply to message #570194] Tue, 06 November 2012 13:11 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Incredible.
Re: Puzzle n°07 - Create A Calendar for the Given Month and Year * [message #570232 is a reply to message #570199] Wed, 07 November 2012 02:56 Go to previous message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Its an Old one On Forums.oracle ...But Awesome work !

Sriram
Previous Topic: Puzzle n°14 - Calculating hardcoded arithmetic expressions and formulas ***
Next Topic: Latest Oracle Version
Goto Forum:
  


Current Time: Tue Sep 02 10:20:09 CDT 2014

Total time taken to generate the page: 0.07441 seconds