select
(
case UnitFlg.Sexcd when 1 then '男性'
when 2 then '女性'
else '総計' end
) sex,
-- UnitFlg.bSexcd,
(case UnitFlg.agescope when 0 then '30~39'
when 1 then '40~49'
when 2 then '50~59'
when 3 then '60~69' end) AgeS,
-- UnitFlg.agescope,
(case UnitFlg.Docshuruino||UnitFlg.Jushinkenumuflg
when '11' then '人間ドック'
when '12' then '人間ドック・券'
when '19' then '人間ドック計'
when '21' then '脳ドック'
when '22' then '脳ドック・券'
when '29' then '脳ドック計'
when '31' then '人間ドック・脳'
when '32' then '人間ドック・脳・券'
when '39' then '人間ドック・脳計'
else '合計'
end) shurui,
-- UnitFlg.Docshuruino,
-- UnitFlg.Jushinkenumuflg,
nvl(detail.Year,0) Year,
nvl(detail.Jua,0) Jua,
nvl(detail.Feb,0) Feb,
nvl(detail.Mar,0) Mar,
nvl(detail.Apr,0) Apr,
nvl(detail.May,0) May,
nvl(detail.Jun,0) Jun,
nvl(detail.Jul,0) Jul,
nvl(detail.Aug,0) Aug,
nvl(detail.Sep,0) Sep,
nvl(detail.Oct,0) Oct,
nvl(detail.Nve,0) Nve,
nvl(detail.Dec,0) Dec
from
---------------------------------------------------------------Header----------------------------------------------------------------------------------------------
(Select
nvl(sexcd,3) sexcd,--集計
agescope,--年代
nvl(Docshuruino,9) Docshuruino,--種類
nvl(Jushinkenumuflg,9) Jushinkenumuflg--種類
From
( Select 1 sexcd From dual Union Select 2 From dual) sexcd,
( Select 0 ageScope From dual Union Select 1 From dual Union Select 2 From dual Union
Select 3 From dual) agescope,
( Select 1 Docshuruino From dual Union Select 2 From dual Union Select 3 From dual) Docshuruino,
(Select 1 Jushinkenumuflg From dual Union Select 2 From dual ) Jushinkenumuflg
-- Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
Order By sexcd,agescope,Docshuruino,Jushinkenumuflg) UnitFlg,
--------------------------------------------------------------Detail-------------------------------------------------------------------------------------------------
(select
nvl(x.Sexcd,3) sexcd,--集計
x.agescope,--年代
nvl(x.Docshuruino,9) Docshuruino,--種類
nvl(x.Jushinkenumuflg,9) Jushinkenumuflg,--種類
count(x.months) Year, --合計
count(case when x.months='1' then x.months end) Jua,--1月
count(case when x.months='2' then x.months end) Feb,--2月
count(case when x.months='3' then x.months end) Mar,--3月
count(case when x.months='4' then x.months end) Apr,--4月
count(case when x.months='5' then x.months end) May,--5月
count(case when x.months='6' then x.months end) Jun,--6月
count(case when x.months='7' then x.months end) Jul,--7月
count(case when x.months='8' then x.months end) Aug,--8月
count(case when x.months='9' then x.months end) Sep,--9月
count(case when x.months='10' then x.months end) Oct,--10月
count(case when x.months='11' then x.months end) Nve,--11月
count(case when x.months='12' then x.months end) Dec--12月
from
-------------------------------------------------PreHandle_Start----------------------------------------------------------------------------
(select Kojinno,
Docshuruino,
Jushinkenumuflg,
Sexcd,
Age,
(
case
when Age between 30 and 39 then 0
when Age between 40 and 49 then 1
when Age between 50 and 59 then 2
when Age between 60 and 69 then 3
end
) AgeScope,
Jushinorderdate,
Months
from (
Select A.Kojinno,
A.Docshuruino,
A.Jushinkenumuflg,
B.Sexcd,
TRUNC(MONTHS_BETWEEN(DECODE(c.Nenreicalckbn,
2,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0331'),
3,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0401'),
Sysdate),
b.BirthDay)/12, 0
) Age,
to_char(a.Jushinorderdate,'yyyymmdd') Jushinorderdate,
(
case
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0401','yyyymmdd') and to_date(to_char(a.nendo)||'0430','yyyymmdd') then '4'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0501','yyyymmdd') and to_date(to_char(a.nendo)||'0531','yyyymmdd') then '5'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0601','yyyymmdd') and to_date(to_char(a.nendo)||'0630','yyyymmdd') then '6'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0701','yyyymmdd') and to_date(to_char(a.nendo)||'0731','yyyymmdd') then '7'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0801','yyyymmdd') and to_date(to_char(a.nendo)||'0831','yyyymmdd') then '8'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0901','yyyymmdd') and to_date(to_char(a.nendo)||'0930','yyyymmdd') then '9'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1001','yyyymmdd') and to_date(to_char(a.nendo)||'1031','yyyymmdd') then '10'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1101','yyyymmdd') and to_date(to_char(a.nendo)||'1130','yyyymmdd') then '11'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1201','yyyymmdd') and to_date(to_char(a.nendo)||'1231','yyyymmdd') then '12'
when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0101','yyyymmdd') and to_date(to_char(a.nendo+1)||'0131','yyyymmdd') then '1'
when to_char(a.Jushinorderdate,'yyyymmdd')>=(a.nendo+1)||'0201' and to_char(a.Jushinorderdate,'yyyymmdd')<=(a.nendo+1)||'0229' then '2'
when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0301','yyyymmdd') and to_date(to_char(a.nendo+1)||'0331','yyyymmdd') then '3'
end
) Months
from kwdocinfo a ,
kojininfo b,
(Select NenreiCalcKbn From SystemConfig Where SystemConfigID = 1) c
where A.Nendo=2008 And
A.Delflg=1 And
A.Kojinno=B.Kojinno And
B.Birthday Is Not Null And
B.SEXCD is not null and
A.Jushinorderdate Is Not Null)
Where Months Is Not Null And
age between 30 and 69 and
sexcd between 1 and 2 and
Docshuruino between 1 and 3 and
Jushinkenumuflg between 1 and 2
) X
-------------------------------------------------------------PreHandle_End------------------------------------------------------------------------------------
--Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
) detail
where UnitFlg.sexcd=detail.sexcd(+) and
unitflg.ageScope=detail.ageScope(+) and
unitflg.Docshuruino=detail.Docshuruino(+) and
unitflg.Jushinkenumuflg=detail.Jushinkenumuflg(+)
(
case UnitFlg.Sexcd when 1 then '男性'
when 2 then '女性'
else '総計' end
) sex,
-- UnitFlg.bSexcd,
(case UnitFlg.agescope when 0 then '30~39'
when 1 then '40~49'
when 2 then '50~59'
when 3 then '60~69' end) AgeS,
-- UnitFlg.agescope,
(case UnitFlg.Docshuruino||UnitFlg.Jushinkenumuflg
when '11' then '人間ドック'
when '12' then '人間ドック・券'
when '19' then '人間ドック計'
when '21' then '脳ドック'
when '22' then '脳ドック・券'
when '29' then '脳ドック計'
when '31' then '人間ドック・脳'
when '32' then '人間ドック・脳・券'
when '39' then '人間ドック・脳計'
else '合計'
end) shurui,
-- UnitFlg.Docshuruino,
-- UnitFlg.Jushinkenumuflg,
nvl(detail.Year,0) Year,
nvl(detail.Jua,0) Jua,
nvl(detail.Feb,0) Feb,
nvl(detail.Mar,0) Mar,
nvl(detail.Apr,0) Apr,
nvl(detail.May,0) May,
nvl(detail.Jun,0) Jun,
nvl(detail.Jul,0) Jul,
nvl(detail.Aug,0) Aug,
nvl(detail.Sep,0) Sep,
nvl(detail.Oct,0) Oct,
nvl(detail.Nve,0) Nve,
nvl(detail.Dec,0) Dec
from
---------------------------------------------------------------Header----------------------------------------------------------------------------------------------
(Select
nvl(sexcd,3) sexcd,--集計
agescope,--年代
nvl(Docshuruino,9) Docshuruino,--種類
nvl(Jushinkenumuflg,9) Jushinkenumuflg--種類
From
( Select 1 sexcd From dual Union Select 2 From dual) sexcd,
( Select 0 ageScope From dual Union Select 1 From dual Union Select 2 From dual Union
Select 3 From dual) agescope,
( Select 1 Docshuruino From dual Union Select 2 From dual Union Select 3 From dual) Docshuruino,
(Select 1 Jushinkenumuflg From dual Union Select 2 From dual ) Jushinkenumuflg
-- Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
Order By sexcd,agescope,Docshuruino,Jushinkenumuflg) UnitFlg,
--------------------------------------------------------------Detail-------------------------------------------------------------------------------------------------
(select
nvl(x.Sexcd,3) sexcd,--集計
x.agescope,--年代
nvl(x.Docshuruino,9) Docshuruino,--種類
nvl(x.Jushinkenumuflg,9) Jushinkenumuflg,--種類
count(x.months) Year, --合計
count(case when x.months='1' then x.months end) Jua,--1月
count(case when x.months='2' then x.months end) Feb,--2月
count(case when x.months='3' then x.months end) Mar,--3月
count(case when x.months='4' then x.months end) Apr,--4月
count(case when x.months='5' then x.months end) May,--5月
count(case when x.months='6' then x.months end) Jun,--6月
count(case when x.months='7' then x.months end) Jul,--7月
count(case when x.months='8' then x.months end) Aug,--8月
count(case when x.months='9' then x.months end) Sep,--9月
count(case when x.months='10' then x.months end) Oct,--10月
count(case when x.months='11' then x.months end) Nve,--11月
count(case when x.months='12' then x.months end) Dec--12月
from
-------------------------------------------------PreHandle_Start----------------------------------------------------------------------------
(select Kojinno,
Docshuruino,
Jushinkenumuflg,
Sexcd,
Age,
(
case
when Age between 30 and 39 then 0
when Age between 40 and 49 then 1
when Age between 50 and 59 then 2
when Age between 60 and 69 then 3
end
) AgeScope,
Jushinorderdate,
Months
from (
Select A.Kojinno,
A.Docshuruino,
A.Jushinkenumuflg,
B.Sexcd,
TRUNC(MONTHS_BETWEEN(DECODE(c.Nenreicalckbn,
2,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0331'),
3,TO_DATE(TO_CHAR(TO_NUMBER(a.Nendo) + 1) || '0401'),
Sysdate),
b.BirthDay)/12, 0
) Age,
to_char(a.Jushinorderdate,'yyyymmdd') Jushinorderdate,
(
case
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0401','yyyymmdd') and to_date(to_char(a.nendo)||'0430','yyyymmdd') then '4'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0501','yyyymmdd') and to_date(to_char(a.nendo)||'0531','yyyymmdd') then '5'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0601','yyyymmdd') and to_date(to_char(a.nendo)||'0630','yyyymmdd') then '6'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0701','yyyymmdd') and to_date(to_char(a.nendo)||'0731','yyyymmdd') then '7'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0801','yyyymmdd') and to_date(to_char(a.nendo)||'0831','yyyymmdd') then '8'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'0901','yyyymmdd') and to_date(to_char(a.nendo)||'0930','yyyymmdd') then '9'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1001','yyyymmdd') and to_date(to_char(a.nendo)||'1031','yyyymmdd') then '10'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1101','yyyymmdd') and to_date(to_char(a.nendo)||'1130','yyyymmdd') then '11'
when a.Jushinorderdate between to_date(to_char(a.nendo)||'1201','yyyymmdd') and to_date(to_char(a.nendo)||'1231','yyyymmdd') then '12'
when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0101','yyyymmdd') and to_date(to_char(a.nendo+1)||'0131','yyyymmdd') then '1'
when to_char(a.Jushinorderdate,'yyyymmdd')>=(a.nendo+1)||'0201' and to_char(a.Jushinorderdate,'yyyymmdd')<=(a.nendo+1)||'0229' then '2'
when a.Jushinorderdate between to_date(to_char(a.nendo+1)||'0301','yyyymmdd') and to_date(to_char(a.nendo+1)||'0331','yyyymmdd') then '3'
end
) Months
from kwdocinfo a ,
kojininfo b,
(Select NenreiCalcKbn From SystemConfig Where SystemConfigID = 1) c
where A.Nendo=2008 And
A.Delflg=1 And
A.Kojinno=B.Kojinno And
B.Birthday Is Not Null And
B.SEXCD is not null and
A.Jushinorderdate Is Not Null)
Where Months Is Not Null And
age between 30 and 69 and
sexcd between 1 and 2 and
Docshuruino between 1 and 3 and
Jushinkenumuflg between 1 and 2
) X
-------------------------------------------------------------PreHandle_End------------------------------------------------------------------------------------
--Group By Grouping Sets((sexcd,agescope,Docshuruino,Jushinkenumuflg),(sexcd,agescope,Docshuruino),(sexcd,agescope))
Group by rollup(sexcd),ageScope,rollup(Docshuruino,Jushinkenumuflg)
) detail
where UnitFlg.sexcd=detail.sexcd(+) and
unitflg.ageScope=detail.ageScope(+) and
unitflg.Docshuruino=detail.Docshuruino(+) and
unitflg.Jushinkenumuflg=detail.Jushinkenumuflg(+)