Wednesday, January 9, 2008

Current month calendar (model clause)

I know there were plenty calendar creation scripts through SQL, but I couldn't find any using the Model clause.

So here what I wrote:

SQL> create or replace view calendar_view as
2 select case when dim=0 then to_char(dw1,' DY')
3 when trunc(sysdate,'mm')<>trunc(dw1,'mm') then null
4 when trunc(sysdate)=dw1 then '['||to_char(dw1,'dd')||']'
5 else to_char(dw1,' dd')
6 end dw1,
7 case when dim=0 then to_char(dw2,' DY')
8 when trunc(sysdate,'mm')<>trunc(dw2,'mm') then null
9 when trunc(sysdate)=dw2 then '['||to_char(dw2,'dd')||']'
10 else to_char(dw2,' dd')
11 end dw2,
12 case when dim=0 then to_char(dw3,' DY')
13 when trunc(sysdate,'mm')<>trunc(dw3,'mm') then null
14 when trunc(sysdate)=dw3 then '['||to_char(dw3,'dd')||']'
15 else to_char(dw3,' dd')
16 end dw3,
17 case when dim=0 then to_char(dw4,' DY')
18 when trunc(sysdate,'mm')<>trunc(dw4,'mm') then null
19 when trunc(sysdate)=dw4 then '['||to_char(dw4,'dd')||']'
20 else to_char(dw4,' dd')
21 end dw4,
22 case when dim=0 then to_char(dw5,' DY')
23 when trunc(sysdate,'mm')<>trunc(dw5,'mm') then null
24 when trunc(sysdate)=dw5 then '['||to_char(dw5,'dd')||']'
25 else to_char(dw5,' dd')
26 end dw5,
27 case when dim=0 then to_char(dw6,' DY')
28 when trunc(sysdate,'mm')<>trunc(dw6,'mm') then null
29 when trunc(sysdate)=dw6 then '['||to_char(dw6,'dd')||']'
30 else to_char(dw6,' dd')
31 end dw6,
32 case when dim=0 then to_char(dw7,' DY')
33 when trunc(sysdate,'mm')<>trunc(dw7,'mm') then null
34 when trunc(sysdate)=dw7 then '['||to_char(dw7,'dd')||']'
35 else to_char(dw7,' dd')
36 end dw7
37 from dual
38 model
39 dimension by (0 dim)
40 measures(cast(null as date) dw1,
41 cast(null as date) dw2,
42 cast(null as date) dw3,
43 cast(null as date) dw4,
44 cast(null as date) dw5,
45 cast(null as date) dw6,
46 cast(null as date) dw7)
47 rules iterate(7) until (dw7[iteration_number]>last_day(sysdate))
48 (dw1[iteration_number]=trunc(sysdate,'mm')-to_char(trunc(sysdate,'mm'),'d')+1+7*(iteration_number-1),
49 dw2[iteration_number]=dw1[CV()]+1,
50 dw3[iteration_number]=dw1[CV()]+2,
51 dw4[iteration_number]=dw1[CV()]+3,
52 dw5[iteration_number]=dw1[CV()]+4,
53 dw6[iteration_number]=dw1[CV()]+5,
54 dw7[iteration_number]=dw1[CV()]+6
55 )
56 /

View created

SQL> alter session set nls_territory=CIS nls_date_language=RUSSIAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
ПН ВТ СР ЧТ ПТ СБ ВС
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

6 rows selected

SQL> alter session set nls_territory=AMERICA nls_date_language=AMERICAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
SUN MON TUE WED THU FRI SAT
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

6 rows selected

SQL> alter session set nls_territory=GERMANY nls_date_language=GERMAN;

Session altered

SQL> select * from calendar_view;

DW1 DW2 DW3 DW4 DW5 DW6 DW7
---- ---- ---- ---- ---- ---- ----
MO DI MI DO FR SA SO
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

6 rows selected

SQL>
The code is NLS-independant as you can see.
It is pretty easy to understand to my mind, so I'm going to explain it in details as I did with queries in my previous posts, but if you have any questions - you are welcome.

1 comments:

Anonymous said...

good work mate