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