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  |
 |
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 ]
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
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 #360601 is a reply to message #347397] |
Fri, 21 November 2008 10:20 |
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 #360609 is a reply to message #360601] |
Fri, 21 November 2008 10:43 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #360653 is a reply to message #360652] |
Fri, 21 November 2008 18:01 |
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 #360773 is a reply to message #290970] |
Mon, 24 November 2008 00:30 |
|
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
Good.. Nice and rare gintsp . Good search 
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;
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 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
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.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 15:15:15 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
