SELECT 1 AS DISPNO
,'남' AS FLAG
,COUNT(*) AS CNT
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ( '1','2','3','5') THEN 1 ELSE 0 END), 0) AS CNTTOTAL
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 320 THEN 1 ELSE 0 END), 0) AS CNT1 --전혈320ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 400 THEN 1 ELSE 0 END), 0) AS CNT2 --전혈400ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '1' THEN 1 ELSE 0 END), 0) AS CNT3 --성분헌혈/혈소판
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ('2','3') THEN 1 ELSE 0 END), 0) AS CNT4 --성분헌혈/백혈구
FROM LIS.LBDHBIZG BIZG,
LIS.LBDHDNOR DNOR
WHERE BIZG.INSTCD = DNOR.INSTCD
AND BIZG.DNORNO = DNOR.DNORNO
AND BIZG.INSTCD = '{?instcd}'
AND BIZG.EXECDT LIKE '{?execyymm2}' || '%' --수행일시시
AND BIZG.BIZLRGKIND = '1' --헌혈
AND DNOR.DNORSEX = 'M' --성별
AND BIZG.BIZCNTSHISTCD != 'L'
UNION ALL
SELECT 2 AS DISPNO
,'여' AS FLAG
,COUNT(*) AS CNT
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ( '1','2','3','5') THEN 1 ELSE 0 END), 0) AS CNTTOTAL
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 320 THEN 1 ELSE 0 END), 0) AS CNT1 --전혈320ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 400 THEN 1 ELSE 0 END), 0) AS CNT2 --전혈400ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '1' THEN 1 ELSE 0 END), 0) AS CNT3 --성분헌혈/혈소판
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ('2','3') THEN 1 ELSE 0 END), 0) AS CNT4 --성분헌혈/백혈구
FROM LIS.LBDHBIZG BIZG,
LIS.LBDHDNOR DNOR
WHERE BIZG.INSTCD = DNOR.INSTCD
AND BIZG.DNORNO = DNOR.DNORNO
AND BIZG.INSTCD = '{?instcd}'
AND BIZG.EXECDT LIKE '{?execyymm2}' || '%' --수행일시시
AND BIZG.BIZLRGKIND = '1' --헌혈
AND DNOR.DNORSEX = 'F' --성별
AND BIZG.BIZCNTSHISTCD != 'L'
UNION ALL
SELECT 3 AS DISPNO
,'월계' AS FLAG
,COUNT(*) AS CNT
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ( '1','2','3','5') THEN 1 ELSE 0 END), 0) AS CNTTOTAL
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 320 THEN 1 ELSE 0 END), 0) AS CNT1 --전혈320ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 400 THEN 1 ELSE 0 END), 0) AS CNT2 --전혈400ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '1' THEN 1 ELSE 0 END), 0) AS CNT3 --성분헌혈/혈소판
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ('2','3') THEN 1 ELSE 0 END), 0) AS CNT4 --성분헌혈/백혈구
FROM LIS.LBDHBIZG BIZG,
LIS.LBDHDNOR DNOR
WHERE BIZG.INSTCD = DNOR.INSTCD
AND BIZG.DNORNO = DNOR.DNORNO
AND BIZG.INSTCD = '{?instcd}'
AND BIZG.EXECDT LIKE '{?execyymm2}' || '%' --수행일시시
AND BIZG.BIZLRGKIND = '1' --헌혈
AND DNOR.DNORSEX IN ('F','M') --성별
AND BIZG.BIZCNTSHISTCD != 'L'
UNION ALL
SELECT 4 AS DISPNO
,'누계' AS FLAG
,COUNT(*) AS CNT
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ( '1','2','3','5') THEN 1 ELSE 0 END), 0) AS CNTTOTAL
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 320 THEN 1 ELSE 0 END), 0) AS CNT1 --전혈320ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '5' AND BIZG.DONVOL = 400 THEN 1 ELSE 0 END), 0) AS CNT2 --전혈400ML
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND = '1' THEN 1 ELSE 0 END), 0) AS CNT3 --성분헌혈/혈소판
,NVL(SUM(CASE WHEN BIZG.BIZMDLKIND IN ('2','3') THEN 1 ELSE 0 END), 0) AS CNT4 --성분헌혈/백혈구
FROM LIS.LBDHBIZG BIZG,
LIS.LBDHDNOR DNOR
WHERE BIZG.INSTCD = DNOR.INSTCD
AND BIZG.DNORNO = DNOR.DNORNO
AND BIZG.INSTCD = '{?instcd}'
AND BIZG.EXECDT >= SUBSTR('{?execyymm2}', 0, 4) || '0000000000'
AND BIZG.EXECDT <= '{?execyymm2}' || '999999'
AND BIZG.BIZLRGKIND = '1' --헌혈
AND DNOR.DNORSEX IN ('F','M') --남자
AND BIZG.BIZCNTSHISTCD != 'L'
ORDER BY 1,2