7-63 勤務履歴の作成

SQLパズル

勤務履歴
 Sname  shift  qty  Sdate
------  -----  ---  ----------
 Smtih  AM      10  2006/03/20
 Smith  PM      20  2006/03/20
 Smith  PM      20  2006/03/21
Martin  AM      15  2006/03/20
Martin  PM      25  2006/03/20
Martin  AM      20  2006/03/21

Sname別、Sdate別の、勤務履歴を作成する。

出力順は、
小計の昇順、Sdateの昇順で、総合計は最後とする。

出力結果
 Sname  AM  PM  Total  Sdate
------  --  --  -----  ----------
 Smtih  10  20     30  2006/03/20
 Smtih   0  20     20  2006/03/21
 Smtih  10  40     50  小計
Martin  15  25     40  2006/03/20
Martin  20   0     20  2006/03/21
Martin  35  25     60  小計
総合計  45  65    110  総合計

データ作成スクリプト

create table 勤務履歴 as
select 'Smith' as Sname,'AM' as shift,10 as qty,
      to_date('2006/03/20','YYYY/MM/DD') as Sdate from dual
union select 'Smith' ,'PM',20,to_date('2006/03/20','YYYY/MM/DD') from dual
union select 'Smith' ,'PM',20,to_date('2006/03/21','YYYY/MM/DD') from dual
union select 'Martin','AM',15,to_date('2006/03/20','YYYY/MM/DD') from dual
union select 'Martin','PM',25,to_date('2006/03/20','YYYY/MM/DD') from dual
union select 'Martin','AM',20,to_date('2006/03/21','YYYY/MM/DD') from dual;

SQL

col Sname for a10
col AM for 99
col PM for 99
col Total for 99999
col Sdate for a10

--■■■方法１■■■
select case when 1 = all(grouping(Sname),grouping(Sdate))
       then '総合計' else Sname end as Sname,
sum(decode(shift,'AM',qty,0)) as AM,
sum(decode(shift,'PM',qty,0)) as PM,
sum(qty) as Total,
case when 1 = all(grouping(Sname),grouping(Sdate)) then '総合計'
     when grouping(Sdate) = 1 then '小計'
     else to_char(Sdate,'YYYY/MM/DD') end as Sdate
from 勤務履歴
group by RollUp(Sname,Sdate)
order by
decode(Sname,'総合計',1,0),
sum(Total) over(partition by Sname),
nullif(Sdate,'小計');

--■■■方法２■■■
select case when grouping(Sname) = 1
       then '総合計' else Sname end as Sname,
sum(decode(shift,'AM',qty,0)) as AM,
sum(decode(shift,'PM',qty,0)) as PM,
sum(qty) as Total,
case when grouping(Sname) = 1 then '総合計'
     when grouping(Sdate) = 1 then '小計'
     else to_char(Sdate,'YYYY/MM/DD') end as Sdate
from 勤務履歴
group by RollUp(Sname,Sdate)
order by
decode(Sname,'総合計',1,0),
sum(Total) over(partition by Sname),
nullif(Sdate,'小計');

解説

decode関数とクロス集計を組み合わせてます。

方法２では、RollUpを使った集計では、
grouping(Sname)=1 ならば grouping(Sdate)=1
が成立することを使ってます。

---