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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
| With t As
(
Select 8 As mth, 2009 As yr From Dual Union All
Select 2 , 1900 From Dual Union All
Select 10 , 1959 From Dual
)
Select Decode (Sections.num,
1, '+-----------------------------+',
2, '|' || Lpad (Rpad (Calendar_4.month_name,
Length(Calendar_4.month_name) + Ceil ( (29-Length(Calendar_4.month_name))/2) ),
29) || '|',
3, '|=============================|',
4, Calendar_4.days_list,
5, '|-----------------------------|',
6, Calendar_4.week_line,
7, '+-----------------------------+') As Calendar
From ( /* Calendar_4: Create a line for each week of month */
Select Calendar_3.the_year, Calendar_3.the_month, Calendar_3.Line_number, Calendar_3.month_name,
( '|'
|| Max (Lpad (Decode (Calendar_3.Column_number, 1, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 2, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 3, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 4, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 5, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 6, Calendar_3.day_name, ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 7, Calendar_3.day_name, ' '), 4))
|| ' |') As days_list,
( '|'
|| Max (Lpad (Decode (Calendar_3.Column_number, 1, To_Char (Calendar_3.the_day), ' '), 3))
|| Max (Lpad (Decode (Calendar_3.Column_number, 2, To_Char (Calendar_3.the_day), ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 3, To_Char (Calendar_3.the_day), ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 4, To_Char (Calendar_3.the_day), ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 5, To_Char (Calendar_3.the_day), ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 6, To_Char (Calendar_3.the_day), ' '), 4))
|| Max (Lpad (Decode (Calendar_3.Column_number, 7, To_Char (Calendar_3.the_day), ' '), 4))
|| ' |') As week_line
From ( /* Calendar_3: Compute the line and column of each day in the month */
Select Calendar_2.the_year, Calendar_2.the_month, Calendar_2.the_day, Calendar_2.the_date,
Trim (To_Char (Calendar_2.the_date, 'MONTH')) || ' ' || Trim (To_Char (Calendar_2.the_year)) As month_name,
Rtrim (To_Char (Calendar_2.the_date, 'Dy'), '.') As day_name,
Count (Calendar_2.Start_day_of_line) Over (Partition By Calendar_2.the_year, Calendar_2.the_month
Order By Calendar_2.the_day Asc) As Line_number,
Mod (To_Number (To_Char (Calendar_2.the_date, 'D')), 7) + 1 As Column_number
From ( /* Calendar_2: Compute the day that starts each line to identify this line */
Select Calendar_1.the_year, Calendar_1.the_month, Calendar_1.the_day, Calendar_1.the_date,
Case When To_Number (To_Char (Calendar_1.the_date, 'D')) = 7 /* if sunday or first day of month */
Or To_Number (To_Char (Calendar_1.the_date, 'DD')) = 1
Then Calendar_1.the_day
End Start_day_of_line
From ( /* Calendar_1: Compute the days of each month */
Select months.yr As the_year,
months.mth As the_month,
days.num As the_day,
(months.mth_first_day + days.num - 1) As the_date
From (Select t.mth, t.yr,
To_Date ('01' || Trim (To_Char (t.mth, '00')) || Trim (To_Char (t.yr)), 'DDMMYYYY') As mth_first_day,
Extract (Day From Last_Day (To_Date ('01' || Trim (To_Char (t.mth, '00')) || Trim (To_Char (t.yr)), 'DDMMYYYY'))) As mth_last_day
From t) months,
(Select Level num
From Dual
Connect By Level <= 31 ) days
Where days.num <= months.mth_last_day
--Order By months.yr, months.mth, days.num
) Calendar_1
) Calendar_2
) Calendar_3
Group By Calendar_3.the_year, Calendar_3.the_month, Calendar_3.Line_number, Calendar_3.month_name
) Calendar_4,
(Select Level As num
From Dual
Connect By Level <= 7) Sections
Where /* Section 6 is the lines of dates */
/* For other sections, we need just 1 full line */
Calendar_4.Line_number = 2
Or Sections.num = 6
Order By Calendar_4.the_year,
Calendar_4.the_month,
Sections.num,
Calendar_4.Line_number
; |
