20. April 2011

Ein wenig Spielerei: Monatskalender in SQL Plus!

Playing around a bit: Monthly calendar in SQL*Plus
Heute habe ich etwas, was wohl auch ein wenig in die Kategorie "Spielereien" gehört, aber die muss es ja auch geben. Zuletzt hatte ich mal ein paar Minuten und dachte mir, dass ein SQL-Skript zum Generieren eines Monatskalenders doch ganz nett wäre - die Grundversion mit der PIVOT-Klausel war schnell erstellt - dann fehlte nur noch der Feinschliff. Und hier ist es.
My today's blog posting might be something for the toybox - but we also need those things. Last week I had some spare minutes in which I was playing around with the SQL PIVOT clause introduced in Oracle11g. So I created a monthly calendar as a SQL script. Here we go.
ol monat format a15
set pages 20
set verify off

with dates as (
  select  to_date('01&1', 'DDMMYYYY') + (level - 1) the_date from dual
  connect by level < trunc((add_months(to_date('01&2','DDMMYYYY'),1) - 1) - to_date('01&1', 'DDMMYYYY')) + 2 
) 
select monat, " MO ", " DI ", " MI ", " DO ", " FR ", " SA ", " SO ", ww KW 
from (
  select 
    rtrim(to_char(the_date, 'MONTH' ), 'nls_date_language=german')  monat 
   ,case 
      when to_char(the_date, 'IW') > 50 and to_char(the_date, 'MM') = '01' then 
        to_char(the_date, 'YYYY') - 1||to_char(the_date, 'IW')
      else 
        to_char(the_date, 'YYYYIW')
    end as yyyyww
   ,to_char(the_date, 'yyyyMM' ) m 
   ,case when trunc(the_date) = trunc(sysdate) then 
      '*' || to_char(the_date, 'DD') || '*' 
    else 
      ' ' || to_char(the_date, 'DD') || ' '
    end dd
   ,rtrim(upper(to_char(the_date, 'DY', 'nls_date_language=''german''' )))  d 
   ,to_char(the_date, 'IW') ww
  from dates where the_date between to_date('01&1', 'DDMMYYYY') and add_months(to_date('01&2', 'DDMMYYYY'),1) - 1
) pivot (
  min(dd) for d in (
    'MO' as " MO ",
    'DI' as " DI ",
    'MI' as " MI ",
    'DO' as " DO ",
    'FR' as " FR ",
    'SA' as " SA ",
    'SO' as " SO " 
  )
)
order by yyyyww, m
/

undefine 1
undefine 2
Aufrufen geht dann so.
Use it as follows ...
SQL> @kal 042011 052011

MONAT            MO   DI   MI   DO   FR   SA   SO  KW
--------------- ---- ---- ---- ---- ---- ---- ---- --
APRIL                                01   02   03  13
APRIL            04   05   06   07   08   09   10  14
APRIL            11   12   13   14   15   16   17  15
APRIL           *18*  19   20   21   22   23   24  16
APRIL            25   26   27   28   29   30       17
MAI                                            01  17
MAI              02   03   04   05   06   07   08  18
MAI              09   10   11   12   13   14   15  19
MAI              16   17   18   19   20   21   22  20
MAI              23   24   25   26   27   28   29  21
MAI              30   31                           22
Bei der schnellen Terminplanung vielleicht ganz nützlich - nehmt's ansonsten als Beispiel für "Kunst mit SQL Plus". Bei mir liegt es neben einigen anderen Skripten im Verzeichnis, auf das die Umgebungsvariable SQLPATH zeigt; damit habe ich es immer im Zugriff.
Perhaps it is useful, perhaps it's just SQL*Plus arts. I placed it in the SQLPATH folder where it is always accessible. Have fun.

Kommentare:

Martin Preiss hat gesagt…

sehr schön; so was fehlte mir noch, um in sqlplus vollständig autark zu werden...

Der column-Format-Befehl für den Monat scheint übrigens ein paar Buchstaben verloren zu haben.

Gruß

MP

blogje hat gesagt…

Und fur 10G

select to_char( monat, 'MONTH', 'nls_date_language=german' ) monat
, case when mo = heute then '*' || to_char( mo, 'dd' ) || '*' else to_char( mo, 'dd' ) end mo
, case when di = heute then '*' || to_char( di, 'dd' ) || '*' else to_char( di, 'dd' ) end di
, case when mi = heute then '*' || to_char( mi, 'dd' ) || '*' else to_char( mi, 'dd' ) end mi
, case when do = heute then '*' || to_char( do, 'dd' ) || '*' else to_char( do, 'dd' ) end do
, case when fr = heute then '*' || to_char( fr, 'dd' ) || '*' else to_char( fr, 'dd' ) end fr
, case when sa = heute then '*' || to_char( sa, 'dd' ) || '*' else to_char( sa, 'dd' ) end sa
, case when so = heute then '*' || to_char( so, 'dd' ) || '*' else to_char( so, 'dd' ) end so
, to_char( kw, 'IW' ) kw
from ( select monat
, min( case when the_date >= kw and trunc( kw , 'mm' ) = monat then the_date end ) mo
, min( case when the_date >= kw + 1 and trunc( kw + 1, 'mm' ) = monat then the_date end ) di
, min( case when the_date >= kw + 2 and trunc( kw + 2, 'mm' ) = monat then the_date end ) mi
, min( case when the_date >= kw + 3 and trunc( kw + 3, 'mm' ) = monat then the_date end ) do
, min( case when the_date >= kw + 4 and trunc( kw + 4, 'mm' ) = monat then the_date end ) fr
, min( case when the_date >= kw + 5 and trunc( kw + 5, 'mm' ) = monat then the_date end ) sa
, min( case when the_date >= kw + 6 and trunc( kw + 6, 'mm' ) = monat then the_date end ) so
, kw
, trunc( sysdate ) heute
from ( select anfang + level - 1 the_date
, trunc( anfang + level - 1, 'IW' ) kw
, trunc( anfang + level - 1, 'mm' ) monat
from ( select to_date( '&1', 'mmyyyy' ) anfang
, add_months( to_date( '&2', 'mmyyyy' ), 1 ) ende
from dual
)
connect by anfang + level <= ende
)
group by monat
, kw
)
order by kw

Beliebte Postings