Today I saw a query that prints a calendar for an entire year. I modified it slightly to conform with my local (Dutch) settings:
- using ISO-weeknumbers
- a week starts with monday and ends on a sunday
If your country uses different settings, then it's probably easy to adjust the query a little to fit your local settings. You'll also have to translate the column names, but that won't be hard either.
rwijk@ORA11G> column maand format a17
rwijk@ORA11G> column week format a4
rwijk@ORA11G> break on maand skip 1
rwijk@ORA11G> with input as (select sysdate dt from dual)
2 select to_char(mnth,'fmMonth yyyy') "Maand"
3 , monday "Ma"
4 , tuesday "Di"
5 , wednesday "Wo"
6 , thursday "Do"
7 , friday "Vr"
8 , saturday "Za"
9 , sunday "Zo"
10 , lpad(to_char(week,'fmiw'),4) "Week"
11 from ( select day
12 , mod(to_number(to_char(day,'j')),7) daynumber_in_week
13 , trunc(day,'iw') week
14 , trunc(day,'mm') mnth
15 , to_char(day,'iyyy') year
16 from ( select trunc(dt,'y') - 1 + level day
17 from input
18 connect by level <= add_months(trunc(dt,'y'),12) - trunc(dt,'y')
19 )
20 )
21 pivot
22 ( max(lpad(to_char(day,'fmdd'),2))
23 for daynumber_in_week in
24 ( 0 monday, 1 tuesday, 2 wednesday, 3 thursday
25 , 4 friday, 5 saturday, 6 sunday
26 )
27 )
28 order by mnth
29 , year
30 , week
31 /
Maand Ma Di Wo Do Vr Za Zo Week
----------------- -- -- -- -- -- -- -- ----
Januari 2008 1 2 3 4 5 6 1
7 8 9 10 11 12 13 2
14 15 16 17 18 19 20 3
21 22 23 24 25 26 27 4
28 29 30 31 5
Februari 2008 1 2 3 5
4 5 6 7 8 9 10 6
11 12 13 14 15 16 17 7
18 19 20 21 22 23 24 8
25 26 27 28 29 9
Maart 2008 1 2 9
3 4 5 6 7 8 9 10
10 11 12 13 14 15 16 11
17 18 19 20 21 22 23 12
24 25 26 27 28 29 30 13
31 14
April 2008 1 2 3 4 5 6 14
7 8 9 10 11 12 13 15
14 15 16 17 18 19 20 16
21 22 23 24 25 26 27 17
28 29 30 18
Mei 2008 1 2 3 4 18
5 6 7 8 9 10 11 19
12 13 14 15 16 17 18 20
19 20 21 22 23 24 25 21
26 27 28 29 30 31 22
Juni 2008 1 22
2 3 4 5 6 7 8 23
9 10 11 12 13 14 15 24
16 17 18 19 20 21 22 25
23 24 25 26 27 28 29 26
30 27
Juli 2008 1 2 3 4 5 6 27
7 8 9 10 11 12 13 28
14 15 16 17 18 19 20 29
21 22 23 24 25 26 27 30
28 29 30 31 31
Augustus 2008 1 2 3 31
4 5 6 7 8 9 10 32
11 12 13 14 15 16 17 33
18 19 20 21 22 23 24 34
25 26 27 28 29 30 31 35
September 2008 1 2 3 4 5 6 7 36
8 9 10 11 12 13 14 37
15 16 17 18 19 20 21 38
22 23 24 25 26 27 28 39
29 30 40
Oktober 2008 1 2 3 4 5 40
6 7 8 9 10 11 12 41
13 14 15 16 17 18 19 42
20 21 22 23 24 25 26 43
27 28 29 30 31 44
November 2008 1 2 44
3 4 5 6 7 8 9 45
10 11 12 13 14 15 16 46
17 18 19 20 21 22 23 47
24 25 26 27 28 29 30 48
December 2008 1 2 3 4 5 6 7 49
8 9 10 11 12 13 14 50
15 16 17 18 19 20 21 51
22 23 24 25 26 27 28 52
29 30 31 1
62 rijen zijn geselecteerd.
And if you are not on 11g yet, you cannot use the pivot operator, and you'll have to settle with this one:
rwijk@ORA11G> with input as (select sysdate dt from dual)
2 select to_char(mnth,'fmMonth yyyy') "Maand"
3 , max(decode(daynumber_in_week,0,lpad(to_char(day,'fmdd'),2))) "Ma"
4 , max(decode(daynumber_in_week,1,lpad(to_char(day,'fmdd'),2))) "Di"
5 , max(decode(daynumber_in_week,2,lpad(to_char(day,'fmdd'),2))) "Wo"
6 , max(decode(daynumber_in_week,3,lpad(to_char(day,'fmdd'),2))) "Do"
7 , max(decode(daynumber_in_week,4,lpad(to_char(day,'fmdd'),2))) "Vr"
8 , max(decode(daynumber_in_week,5,lpad(to_char(day,'fmdd'),2))) "Za"
9 , max(decode(daynumber_in_week,6,lpad(to_char(day,'fmdd'),2))) "Zo"
10 , lpad(to_char(week,'fmiw'),4) "Week"
11 from ( select day
12 , trunc(day,'iw') week
13 , trunc(day,'mm') mnth
14 , mod(to_number(to_char(day,'j')),7) daynumber_in_week
15 , to_char(day,'iyyy') year
16 from ( select trunc(dt,'y') - 1 + level day
17 from input
18 connect by level <= add_months(trunc(dt,'y'),12) - trunc(dt,'y')
19 )
20 )
21 group by mnth
22 , year
23 , week
24 order by mnth
25 , year
26 , week
27 /
Maand Ma Di Wo Do Vr Za Zo Week
----------------- -- -- -- -- -- -- -- ----
Januari 2008 1 2 3 4 5 6 1
7 8 9 10 11 12 13 2
14 15 16 17 18 19 20 3
21 22 23 24 25 26 27 4
28 29 30 31 5
Februari 2008 1 2 3 5
4 5 6 7 8 9 10 6
11 12 13 14 15 16 17 7
18 19 20 21 22 23 24 8
25 26 27 28 29 9
Maart 2008 1 2 9
3 4 5 6 7 8 9 10
10 11 12 13 14 15 16 11
17 18 19 20 21 22 23 12
24 25 26 27 28 29 30 13
31 14
April 2008 1 2 3 4 5 6 14
7 8 9 10 11 12 13 15
14 15 16 17 18 19 20 16
21 22 23 24 25 26 27 17
28 29 30 18
Mei 2008 1 2 3 4 18
5 6 7 8 9 10 11 19
12 13 14 15 16 17 18 20
19 20 21 22 23 24 25 21
26 27 28 29 30 31 22
Juni 2008 1 22
2 3 4 5 6 7 8 23
9 10 11 12 13 14 15 24
16 17 18 19 20 21 22 25
23 24 25 26 27 28 29 26
30 27
Juli 2008 1 2 3 4 5 6 27
7 8 9 10 11 12 13 28
14 15 16 17 18 19 20 29
21 22 23 24 25 26 27 30
28 29 30 31 31
Augustus 2008 1 2 3 31
4 5 6 7 8 9 10 32
11 12 13 14 15 16 17 33
18 19 20 21 22 23 24 34
25 26 27 28 29 30 31 35
September 2008 1 2 3 4 5 6 7 36
8 9 10 11 12 13 14 37
15 16 17 18 19 20 21 38
22 23 24 25 26 27 28 39
29 30 40
Oktober 2008 1 2 3 4 5 40
6 7 8 9 10 11 12 41
13 14 15 16 17 18 19 42
20 21 22 23 24 25 26 43
27 28 29 30 31 44
November 2008 1 2 44
3 4 5 6 7 8 9 45
10 11 12 13 14 15 16 46
17 18 19 20 21 22 23 47
24 25 26 27 28 29 30 48
December 2008 1 2 3 4 5 6 7 49
8 9 10 11 12 13 14 50
15 16 17 18 19 20 21 51
22 23 24 25 26 27 28 52
29 30 31 1
62 rijen zijn geselecteerd.
Rob,
ReplyDeleteThat is fantastic. I had seen sample code to generate calendar using MODEL clause, but did not think of this approach. This works in 8i, as well.
Wat vind je van deze?
ReplyDeletehttp://welman.punt.nl/index.php?r=1&id=420365&tbl_archief=0#420365
It´s not to difficult to chanche it and get a whole year. Change SPANISH to your language to see the results in yours.
Essentially it is the same query. But I like "my" query a little more because:
ReplyDelete- the generating of rows is shorter and clearer
- the "semana" subquery is not necessary
- the column names are confusing and not all columns are actually used
But you score points for being first ;-)