Oralce SQL to create calendar information for a specified period of time

--Create table
CREATE TABLE Temp_calendar
(
MONTH VARCHAR2 (6),
W_7 VARCHAR2 (2),
W_1 VARCHAR2 (2),
W_2 VARCHAR2 (2),
W_3 VARCHAR2 (2),
W_4 VARCHAR2 (2),
W_5 VARCHAR2 (2),
W_6 VARCHAR2 (2),
WEEK VARCHAR2 (20)
) ;
--ADD comments to the table
Comment on table Temp_calendar
Is ' date source data table ';
--ADD comments to the columns
Comment on column Temp_calendar. MONTH
Is ' month (format: 200801) ';
Comment on column Temp_calendar. W_7
Is ' Sunday date (eg: 06) ';
Comment on column Temp_calendar. W_1
Is ' Monday (eg: 07) ';
Comment on column Temp_calendar. W_2
Is ' Tuesday (eg: 08) ';
Comment on column Temp_calendar. W_3
Is ' Wednesday (eg: 09) ';
Comment on column Temp_calendar. W_4
Is ' Thursday (eg: 10) ';
Comment on column Temp_calendar. W_5
Is ' Friday (eg: 11) ';
Comment on column Temp_calendar. W_6
Is ' Saturday (eg: 12) ';
Comment on column Temp_calendar. WEEK
Is ' the first few weeks of this month ';
----Generate calendar information
--TRUNCATE TABLE calendar;
Declare


Vi_begin_year number (6);
Vi_end_year number (6);
Vi_year number (6);
Vi_month number (6);
Vs_month VARCHAR2 (6);
Vs_first_day VARCHAR2 (8);




Begin


Vi_begin_year: = 2000; ---start year
Vi_end_year: = 2100; ---end year
Vi_month: = 1;
Vi_year: = Vi_begin_year;


Loop exit when (Vi_year > Vi_end_year);
For I in 1..12 loop
If Length (i) =1 then
Vs_month: = To_char (vi_year) | | ' 0 ' | | To_char (i);
Else
Vs_month: = To_char (vi_year) | | To_char (i);
End If;

Vs_first_day: = vs_month| | ' 01 ';

INSERT into Temp_calendar (month, week,w_7, W_1, W_2, W_3, W_4, W_5, W_6)

Select substr (vs_first_day,1,6) month,
Ceil ((To_char (everyday, ' DD ') + (To_char (to_date (vs_first_day, ' yyyymmdd '), ' d ')-1))/7) as week,
SUM (Decode (To_char (everyday, ' dy '), ' Sunday ', to_char (everyday, ' DD '))) as Sunday,
SUM (Decode (To_char (everyday, ' dy '), ' Monday ', to_char (everyday, ' DD '))) as Monday,
SUM (Decode (To_char (everyday, ' dy '), ' Tuesday ', to_char (everyday, ' DD '))) as Tuesday,
SUM (Decode (To_char (everyday, ' dy '), ' Wednesday ', to_char (everyday, ' DD '))) as Wednesday,
SUM (Decode (To_char (everyday, ' dy '), ' Thursday ', to_char (everyday, ' DD '))) as Thursday,
SUM (Decode (To_char (everyday, ' dy '), ' Friday ', to_char (everyday, ' DD '))) as Friday,
SUM (Decode (To_char (everyday, ' dy '), ' Saturday ', to_char (everyday, ' DD '))) as Saturday
From
Select To_date (vs_first_day, ' yyyymmdd ') + Level-1 as Everyday
From dual
Connect by Level <= (Last_day (To_date (vs_first_day, ' YYYYMMDD '))
-To_date (Vs_first_day, ' YYYYMMDD ') + 1))
Group BY Ceil ((To_char (everyday, ' DD ') + (To_char (to_date (vs_first_day, ' yyyymmdd '), ' d ')-1))/7);


Commit

End Loop;

Vi_year: = vi_year+1;

End Loop;

End
Order by A.year,a.month, A.week;

Oralce SQL to create calendar information for a specified period of time