トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

3-9 カレンダーを表示その2

SQLパズル

前月、今月、来月、二ヶ月後のカレンダーを表示する


SQL

set head off
set pagesize 0

with 
V前月 as
(select to_date(to_char(add_months(last_day(sysdate),-1),'yyyymm') || '01','yyyymmdd') as 初日,
        to_char(add_months(last_day(sysdate),-1),'dd') as 末日 from dual),
V今月 as
(select to_date(to_char(last_day(sysdate),'yyyymm') || '01','yyyymmdd') as 初日,
        to_char(last_day(sysdate),'dd') as 末日 from dual),
V来月 as
(select to_date(to_char(add_months(last_day(sysdate),1),'yyyymm') || '01','yyyymmdd') as 初日,
        to_char(add_months(last_day(sysdate),1),'dd') as 末日 from dual),
V二ヶ月後 as
(select to_date(to_char(add_months(last_day(sysdate),2),'yyyymm') || '01','yyyymmdd') as 初日,
        to_char(add_months(last_day(sysdate),2),'dd') as 末日 from dual)
select '■■■' || to_char(初日,'yyyy') || '年' ||  to_char(初日,'mm') || '月' || '■■■' || chr(10) ||
' 日  月  火  水  木  金  土' || chr(10) || '------------------------------' || chr(10) ||
case when to_char(初日,'d') = '1' then '  1   2   3   4   5   6   7'
     when to_char(初日,'d') = '2' then ' **   1   2   3   4   5   6'
     when to_char(初日,'d') = '3' then ' **  **   1   2   3   4   5'
     when to_char(初日,'d') = '4' then ' **  **  **   1   2   3   4'
     when to_char(初日,'d') = '5' then ' **  **  **  **   1   2   3'
     when to_char(初日,'d') = '6' then ' **  **  **  **  **   1   2'
     when to_char(初日,'d') = '7' then ' **  **  **  **  **  **   1'
     end || chr(10) || 
case when to_char(初日,'d') = '1' then '  8   9  10  11  12  13  14'
     when to_char(初日,'d') = '2' then '  7   8   9  10  11  12  13'
     when to_char(初日,'d') = '3' then '  6   7   8   9  10  11  12'
     when to_char(初日,'d') = '4' then '  5   6   7   8   9  10  11'
     when to_char(初日,'d') = '5' then '  4   5   6   7   8   9  10'
     when to_char(初日,'d') = '6' then '  3   4   5   6   7   8   9'
     when to_char(初日,'d') = '7' then '  2   3   4   5   6   7   8'
     end || chr(10) ||
case when to_char(初日,'d') = '1' then ' 15  16  17  18  19  20  21'
     when to_char(初日,'d') = '2' then ' 14  15  16  17  18  19  20'
     when to_char(初日,'d') = '3' then ' 13  14  15  16  17  18  19'
     when to_char(初日,'d') = '4' then ' 12  13  14  15  16  17  18'
     when to_char(初日,'d') = '5' then ' 11  12  13  14  15  16  17'
     when to_char(初日,'d') = '6' then ' 10  11  12  13  14  15  16'
     when to_char(初日,'d') = '7' then '  9  10  11  12  13  14  15'
     end || chr(10) ||
case when to_char(初日,'d') = '1' then ' 22  23  24  25  26  27  28'
     when to_char(初日,'d') = '2' then ' 21  22  23  24  25  26  27'
     when to_char(初日,'d') = '3' then ' 20  21  22  23  24  25  26'
     when to_char(初日,'d') = '4' then ' 19  20  21  22  23  24  25'
     when to_char(初日,'d') = '5' then ' 18  19  20  21  22  23  24'
     when to_char(初日,'d') = '6' then ' 17  18  19  20  21  22  23'
     when to_char(初日,'d') = '7' then ' 16  17  18  19  20  21  22'
     end || chr(10) ||
case when to_char(初日,'d') = '1' then 
     decode(末日,'28',RPad(' 29 ',4*7,' ** '),
                 '29',RPad(' 29 ',4*7,' ** '),
                 '30',RPad(' 29  30 ',4*7,' ** '),
                 '31',RPad(' 29  30  31 ',4*7,' ** '))
     when to_char(初日,'d') = '2' then ' 28 ' ||
     decode(末日,'28',' **  **  **  **  **  ** ',
                 '29',RPad(' 29 ',4*6,' ** '),
                 '30',RPad(' 29  30 ',4*6,' ** '),
                 '31',RPad(' 29  30  31 ',4*6,' ** '))
     when to_char(初日,'d') = '3' then ' 27  28 ' ||
     decode(末日,'28',' **  **  **  **  ** ',
                 '29',RPad(' 29 ',4*5,' ** '),
                 '30',RPad(' 29  30 ',4*5,' ** '),
                 '31',RPad(' 29  30  31 ',4*5,' ** '))
     when to_char(初日,'d') = '4' then ' 26  27  28 ' ||
     decode(末日,'28',' **  **  **  ** ',
                 '29',RPad(' 29 ',4*4,' ** '),
                 '30',RPad(' 29  30 ',4*4,' ** '),
                 '31',' 29  30  31  ** ')
     when to_char(初日,'d') = '5' then ' 25  26  27  28 ' ||
     decode(末日,'28',' **  **  ** ',
                 '29',RPad(' 29 ',4*3,' ** '),
                 '30',' 29  30  ** ',
                 '31',' 29  30  31 ')
     when to_char(初日,'d') = '6' then ' 24  25  26  27  28 ' ||
     decode(末日,'28',' **  ** ',
                 '29',' 29  **',
                 '30',' 29  30',
                 '31',' 29  30 ' || chr(10) || RPad(' 31 ',4*7,' ** '))
     when to_char(初日,'d') = '7' then ' 23  24  25  26  27  28 ' ||
     decode(末日,'28',' ** ',
                 '29',' 29',
                 '30',' 29 ' || chr(10) || RPad(' 30 ',4*7,' ** '),
                 '31',' 29 ' || chr(10) || RPad(' 30  31 ',4*7,' ** '))
end as カレンダー
from (select 初日,末日 from V前月
union all select 初日,末日 from V今月
union all select 初日,末日 from V来月
union all select 初日,末日 from V二ヶ月後)
order by 1;


解説

末日による分岐と、
月の初日の曜日による分岐を行ってます。

マニュアル(WITH句を使用した計算)
withの資料