Wednesday, February 27, 2008

Calendar

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.

3 comments:

  1. Rob,

    That 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.

    ReplyDelete
  2. Wat vind je van deze?
    http://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.

    ReplyDelete
  3. Essentially it is the same query. But I like "my" query a little more because:

    - 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 ;-)

    ReplyDelete