SELECT t0.rpt1_A5 AS rpt1_A5, sum(t0.rpt1_B5) AS rpt1_B5, sum(t0.rpt1_E5) AS rpt1_E5, sum(t0.rpt1_H5) AS rpt1_H5, sum(t0.rpt1_L5) AS rpt1_L5, sum(t0.rpt1_M5) AS rpt1_M5, sum(t0.rpt1_N5) AS rpt1_N5, sum(t0.rpt1_O5) AS rpt1_O5, sum(t0.rpt1_P5) AS rpt1_P5, sum(t0.rpt1_S5) AS rpt1_S5, sum(t0.rpt1_V5) AS rpt1_V5, sum(t0.rpt1_Y5) AS rpt1_Y5, sum(t0.rpt1_AB5) AS rpt1_AB5, sum(t0.rpt1_AE5) AS rpt1_AE5, sum(t0.rpt1_AH5) AS rpt1_AH5, sum(t0.rpt1_AK5) AS rpt1_AK5, sum(t0.rpt1_AN5) AS rpt1_AN5, sum(t0.rpt1_AQ5) AS rpt1_AQ5, sum(t0.rpt1_AT5) AS rpt1_AT5, sum(t0.rpt1_AU5) AS rpt1_AU5, sum(t0.rpt1_AX5) AS rpt1_AX5, sum(t0.rpt1_AY5) AS rpt1_AY5, sum(t0.rpt1_BB5) AS rpt1_BB5, sum(t0.rpt1_BC5) AS rpt1_BC5, sum(t0.rpt1_BF5) AS rpt1_BF5, sum(t0.rpt1_BG5) AS rpt1_BG5, sum(t0.rpt1_BJ5) AS rpt1_BJ5, sum(t0.rpt1_BK5) AS rpt1_BK5, sum(t0.rpt1_BN5) AS rpt1_BN5, sum(t0.rpt1_BO5) AS rpt1_BO5, sum(t0.rpt1_BR5) AS rpt1_BR5, sum(t0.rpt1_BS5) AS rpt1_BS5, sum(t0.rpt1_BV5) AS rpt1_BV5, sum(t0.rpt1_BW5) AS rpt1_BW5, sum(t0.rpt1_BX5) AS rpt1_BX5, sum(t0.rpt1_CA5) AS rpt1_CA5, sum(t0.rpt1_CB5) AS rpt1_CB5, sum(t0.rpt1_CC5) AS rpt1_CC5, sum(t0.rpt1_CD5) AS rpt1_CD5, sum(t0.rpt1_CF5) AS rpt1_CF5, sum(t0.rpt1_CI5) AS rpt1_CI5, sum(t0.rpt1_CJ5) AS rpt1_CJ5, sum(t0.rpt1_CK5) AS rpt1_CK5, sum(t0.rpt1_CL5) AS rpt1_CL5, sum(t0.rpt1_CN5) AS rpt1_CN5, sum(t0.rpt1_CQ5) AS rpt1_CQ5, sum(t0.rpt1_CR5) AS rpt1_CR5, sum(t0.rpt1_CS5) AS rpt1_CS5, sum(t0.rpt1_CT5) AS rpt1_CT5, sum(t0.rpt1_CV5) AS rpt1_CV5, sum(t0.rpt1_CY5) AS rpt1_CY5, sum(t0.rpt1_CZ5) AS rpt1_CZ5, sum(t0.rpt1_DA5) AS rpt1_DA5, sum(t0.rpt1_DC5) AS rpt1_DC5, sum(t0.rpt1_DF5) AS rpt1_DF5, sum(t0.rpt1_DG5) AS rpt1_DG5, sum(t0.rpt1_DJ5) AS rpt1_DJ5, sum(t0.rpt1_DK5) AS rpt1_DK5, sum(t0.rpt1_DN5) AS rpt1_DN5, sum(t0.rpt1_DO5) AS rpt1_DO5, sum(t0.rpt1_DQ5) AS rpt1_DQ5, sum(t0.rpt1_DR5) AS rpt1_DR5, sum(t0.rpt1_DV5) AS rpt1_DV5, sum(t0.rpt1_DW5) AS rpt1_DW5, sum(t0.rpt1_DY5) AS rpt1_DY5, sum(t0.rpt1_DZ5) AS rpt1_DZ5, sum(t0.rpt1_ED5) AS rpt1_ED5, sum(t0.rpt1_EE5) AS rpt1_EE5, sum(t0.rpt1_EG5) AS rpt1_EG5, sum(t0.rpt1_EH5) AS rpt1_EH5, sum(t0.rpt1_EL5) AS rpt1_EL5, sum(t0.rpt1_EM5) AS rpt1_EM5, sum(t0.rpt1_EO5) AS rpt1_EO5, sum(t0.rpt1_EP5) AS rpt1_EP5, sum(t0.rpt1_ET5) AS rpt1_ET5, sum(t0.rpt1_EU5) AS rpt1_EU5, sum(t0.rpt1_EX5) AS rpt1_EX5, sum(t0.rpt1_EY5) AS rpt1_EY5, sum(t0.rpt1_FB5) AS rpt1_FB5, sum(t0.rpt1_FC5) AS rpt1_FC5, sum(t0.rpt1_FF5) AS rpt1_FF5, sum(t0.rpt1_FG5) AS rpt1_FG5, sum(t0.rpt1_FJ5) AS rpt1_FJ5, max(t0.rpt1_A5_DSC) AS rpt1_A5_DSC FROM (SELECT t0.rpt1_A5 AS rpt1_A5, max(t0.rpt1_A5_DSC) AS rpt1_A5_DSC, sum(t0.rpt1_B5) AS rpt1_B5, sum(t0.rpt1_E5) AS rpt1_E5, sum(t0.rpt1_AH5) AS rpt1_AH5, sum(t0.rpt1_AK5) AS rpt1_AK5, sum(t0.rpt1_AN5) AS rpt1_AN5, sum(t0.rpt1_AQ5) AS rpt1_AQ5, sum(t0.rpt1_AT5) AS rpt1_AT5, sum(t0.rpt1_AU5) AS rpt1_AU5, sum(t0.rpt1_AX5) AS rpt1_AX5, sum(t0.rpt1_AY5) AS rpt1_AY5, sum(t0.rpt1_BB5) AS rpt1_BB5, sum(t0.rpt1_BC5) AS rpt1_BC5, sum(t0.rpt1_BF5) AS rpt1_BF5, sum(t0.rpt1_BG5) AS rpt1_BG5, sum(t0.rpt1_BJ5) AS rpt1_BJ5, sum(t0.rpt1_BK5) AS rpt1_BK5, sum(t0.rpt1_BN5) AS rpt1_BN5, sum(t0.rpt1_BO5) AS rpt1_BO5, sum(t0.rpt1_BR5) AS rpt1_BR5, sum(t0.rpt1_BS5) AS rpt1_BS5, sum(t0.rpt1_BV5) AS rpt1_BV5, sum(t0.rpt1_BW5) AS rpt1_BW5, sum(t0.rpt1_BX5) AS rpt1_BX5, sum(t0.rpt1_CA5) AS rpt1_CA5, sum(t0.rpt1_CB5) AS rpt1_CB5, sum(t0.rpt1_CD5) AS rpt1_CD5, sum(t0.rpt1_CF5) AS rpt1_CF5, sum(t0.rpt1_CK5) AS rpt1_CK5, sum(t0.rpt1_CL5) AS rpt1_CL5, sum(t0.rpt1_CN5) AS rpt1_CN5, sum(t0.rpt1_CQ5) AS rpt1_CQ5, sum(t0.rpt1_CR5) AS rpt1_CR5, sum(t0.rpt1_CT5) AS rpt1_CT5, sum(t0.rpt1_CV5) AS rpt1_CV5, sum(t0.rpt1_CZ5) AS rpt1_CZ5, sum(t0.rpt1_DA5) AS rpt1_DA5, sum(t0.rpt1_DC5) AS rpt1_DC5, sum(t0.rpt1_DF5) AS rpt1_DF5, sum(t0.rpt1_DG5) AS rpt1_DG5, sum(t0.rpt1_DJ5) AS rpt1_DJ5, sum(t0.rpt1_DK5) AS rpt1_DK5, sum(t0.rpt1_DN5) AS rpt1_DN5, sum(t0.rpt1_DO5) AS rpt1_DO5, sum(t0.rpt1_DQ5) AS rpt1_DQ5, sum(t0.rpt1_DR5) AS rpt1_DR5, sum(t0.rpt1_DV5) AS rpt1_DV5, sum(t0.rpt1_DW5) AS rpt1_DW5, sum(t0.rpt1_DY5) AS rpt1_DY5, sum(t0.rpt1_DZ5) AS rpt1_DZ5, sum(t0.rpt1_ED5) AS rpt1_ED5, sum(t0.rpt1_EE5) AS rpt1_EE5, sum(t0.rpt1_EG5) AS rpt1_EG5, sum(t0.rpt1_EH5) AS rpt1_EH5, sum(t0.rpt1_EL5) AS rpt1_EL5, sum(t0.rpt1_EM5) AS rpt1_EM5, sum(t0.rpt1_EO5) AS rpt1_EO5, sum(t0.rpt1_EP5) AS rpt1_EP5, sum(t0.rpt1_ET5) AS rpt1_ET5, sum(t0.rpt1_EU5) AS rpt1_EU5, sum(t0.rpt1_EX5) AS rpt1_EX5, sum(t0.rpt1_EY5) AS rpt1_EY5, sum(t0.rpt1_FF5) AS rpt1_FF5, sum(t0.rpt1_FG5) AS rpt1_FG5, 0 AS rpt1_H5, 0 AS rpt1_S5, 0 AS rpt1_V5, 0 AS rpt1_Y5, 0 AS rpt1_AB5, 0 AS rpt1_AE5, 0 AS rpt1_L5, 0 AS rpt1_M5, 0 AS rpt1_N5, 0 AS rpt1_O5, 0 AS rpt1_P5, 0 AS rpt1_CC5, 0 AS rpt1_CI5, 0 AS rpt1_CJ5, 0 AS rpt1_CS5, 0 AS rpt1_CY5, 0 AS rpt1_FB5, 0 AS rpt1_FC5, 0 AS rpt1_FJ5 FROM (SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t2.NAME) AS rpt1_A5_DSC, sum(CASE WHEN t0.DEPT_CLASS LIKE 'D1%' AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D12') AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D13') AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D14') AND (LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720') THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_CT5,sum(CASE WHEN t0.LSGX='4' AND t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN (ifnull(t0.B06_C5, 0)+ifnull(t0.B06_C6, 0)+ifnull(t0.B06_C9, 0)+ifnull(t0.B06_C10, 0)+ifnull(t0.B06_C16, 0)+ifnull(t0.B06_TB2_C80, 0)+ifnull(t0.B06_C20, 0)+ifnull(t0.B06_C23, 0)+ifnull(t0.B06_C24, 0)+ifnull(t0.B06_C25, 0)+ifnull(t0.B06_C31, 0)+ifnull(t0.B06_TB2_C81, 0)+ifnull(t0.B06_C19, 0))/10 ELSE 0 END) AS rpt1_BF5,sum(CASE WHEN t0.LSGX='4' AND t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN ifnull(t0.B06_C3, 0)/10 ELSE 0 END) AS rpt1_BG5,sum(CASE WHEN t0.LSGX='4' AND t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN ifnull(t0.B06_C53, 0)/10 ELSE 0 END) AS rpt1_BN5,sum(CASE WHEN t0.LSGX='4' AND t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN (((((ifnull(t0.B06_C44, 0)+ifnull(t0.B06_C50, 0)+ifnull(t0.B06_JYFY, 0)+ifnull(t0.B06_ZCCZFY, 0)+ifnull(t0.B06_SJSJFY, 0)+ifnull(t0.B06_DFSDWBZFY, 0)+ifnull(t0.B06_SDSFY, 0)+ifnull(t0.B06_C52, 0))-ifnull(t0.B06_YWHD_CZXMBK, 0))-ifnull(t0.B06_YWHD_KJJF, 0))-ifnull(t0.B06_DWGL_CZXMBK, 0))-ifnull(t0.B06_DWGL_KJJF, 0))/10 ELSE 0 END) AS rpt1_BO5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'D1%' AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D12') AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D13') AND LEFT(t0.DEPT_CLASS, 3) NOT IN ('D14') THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_CD5,sum(CASE WHEN t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN (ifnull(t0.B06_C5, 0)+ifnull(t0.B06_C6, 0)+ifnull(t0.B06_C9, 0)+ifnull(t0.B06_C10, 0)+ifnull(t0.B06_C16, 0)+ifnull(t0.B06_TB2_C80, 0)+ifnull(t0.B06_C20, 0)+ifnull(t0.B06_C23, 0)+ifnull(t0.B06_C24, 0)+ifnull(t0.B06_C25, 0)+ifnull(t0.B06_C31, 0)+ifnull(t0.B06_TB2_C81, 0)+ifnull(t0.B06_C19, 0))/10 ELSE 0 END) AS rpt1_BB5,sum(CASE WHEN t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN ifnull(t0.B06_C3, 0)/10 ELSE 0 END) AS rpt1_BC5,sum(CASE WHEN t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN ifnull(t0.B06_C53, 0)/10 ELSE 0 END) AS rpt1_BJ5,sum(CASE WHEN t1.TYP1_ID='6' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.B06_C2>0 THEN (((((ifnull(t0.B06_C44, 0)+ifnull(t0.B06_C50, 0)+ifnull(t0.B06_JYFY, 0)+ifnull(t0.B06_ZCCZFY, 0)+ifnull(t0.B06_SJSJFY, 0)+ifnull(t0.B06_DFSDWBZFY, 0)+ifnull(t0.B06_SDSFY, 0)+ifnull(t0.B06_C52, 0))-ifnull(t0.B06_YWHD_CZXMBK, 0))-ifnull(t0.B06_YWHD_KJJF, 0))-ifnull(t0.B06_DWGL_CZXMBK, 0))-ifnull(t0.B06_DWGL_KJJF, 0))/10 ELSE 0 END) AS rpt1_BK5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'D6%' AND t0.CWSS_C11 IN (2,3) AND (LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720') THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_CR5,sum(CASE WHEN (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') THEN ifnull(t0.B03_C8, 0) ELSE 0 END) AS rpt1_DJ5,sum(CASE WHEN (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_DK5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C4, 0) ELSE 0 END) AS rpt1_DQ5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DR5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C4, 0) ELSE 0 END) AS rpt1_DY5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DZ5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C18, 0) ELSE 0 END) AS rpt1_EG5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_EH5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C18, 0) ELSE 0 END) AS rpt1_EO5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_EP5,sum(CASE WHEN t0.JJLX IN ('11','12') AND t0.LSGX='4' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') THEN ifnull(t0.B07_C9, 0)+ifnull(t0.B07_C10, 0) ELSE 0 END) AS rpt1_EX5,sum(CASE WHEN t0.JJLX IN ('11','12') AND t0.LSGX='4' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') THEN ifnull(t0.B07_C2, 0) ELSE 0 END) AS rpt1_EY5,count(CASE WHEN t0.DEPT_CLASS LIKE 'C2%' AND t0.B01_TB2_C61>0 THEN ifnull(t0.USERID_, '0') END) AS rpt1_AT5,count(CASE WHEN t0.DEPT_CLASS LIKE 'D6%' AND t0.CWSS_C22=1 THEN ifnull(t0.USERID_, '0') END) AS rpt1_AX5,sum(CASE WHEN (LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720') AND (t0.B08_C2>0 OR t0.B08_C14>0 OR t0.B08_C16>0 OR t0.B08_C17>0) THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_BR5,sum(CASE WHEN (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.YYDJ_J IN ('2','3') THEN ifnull(t0.B08_C13, 0) ELSE 0 END) AS rpt1_BV5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'D6%' AND t0.CWSS_C11 IN (2,3) THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_CB5,sum(CASE WHEN (LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('M3') OR LEFT(t0.DEPT_CLASS, 2) IN ('M4') OR (LEFT(t0.DEPT_CLASS, 3) IN ('D12') OR LEFT(t0.DEPT_CLASS, 3) IN ('D13') OR LEFT(t0.DEPT_CLASS, 3) IN ('D14')) OR (LEFT(t0.DEPT_CLASS, 4) IN ('D212') OR LEFT(t0.DEPT_CLASS, 4) IN ('D213') OR LEFT(t0.DEPT_CLASS, 4) IN ('D214') OR LEFT(t0.DEPT_CLASS, 4) IN ('D218'))) AND (LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720') THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_CQ5,sum(CASE WHEN (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t1.TYP1_ID='7' THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DF5,sum(CASE WHEN t0.JJLX IN ('11','12') AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') THEN ifnull(t0.B07_C9, 0)+ifnull(t0.B07_C10, 0) ELSE 0 END) AS rpt1_ET5,sum(CASE WHEN t0.JJLX IN ('11','12') AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') THEN ifnull(t0.B07_C2, 0) ELSE 0 END) AS rpt1_EU5,count(CASE WHEN t0.DEPT_CLASS LIKE 'C2%' THEN ifnull(t0.USERID_, '0') END) AS rpt1_AU5,count(CASE WHEN t0.DEPT_CLASS LIKE 'D6%' THEN ifnull(t0.USERID_, '0') END) AS rpt1_AY5,sum(CASE WHEN LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('M3') OR LEFT(t0.DEPT_CLASS, 2) IN ('M4') OR (LEFT(t0.DEPT_CLASS, 3) IN ('D12') OR LEFT(t0.DEPT_CLASS, 3) IN ('D13') OR LEFT(t0.DEPT_CLASS, 3) IN ('D14')) OR (LEFT(t0.DEPT_CLASS, 4) IN ('D212') OR LEFT(t0.DEPT_CLASS, 4) IN ('D213') OR LEFT(t0.DEPT_CLASS, 4) IN ('D214') OR LEFT(t0.DEPT_CLASS, 4) IN ('D218')) THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_CA5,sum(CASE WHEN LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720' THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_CV5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%' THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DG5,sum(CASE WHEN LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) THEN ifnull(t0.XYWCYRS, 0) ELSE 0 END) AS rpt1_FF5,sum(CASE WHEN LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_FG5,sum(ifnull(t0.B02_C5, 0)+ifnull(t0.B02_C10, 0)) AS rpt1_B5,sum(ifnull(t0.B02_C19, 0)) AS rpt1_E5,sum(ifnull(t0.B02_C16, 0)+ifnull(t0.B02_C17, 0)+ifnull(t0.XCQKZYZLYSRS, 0)) AS rpt1_AH5,sum(ifnull(t0.B02_C16, 0)+ifnull(t0.XCQKZYZLYSRS, 0)) AS rpt1_AK5,sum(ifnull(t0.B02_C7, 0)+ifnull(t0.B02_C12, 0)) AS rpt1_AN5,sum(ifnull(t0.B02_C21, 0)) AS rpt1_AQ5,sum(ifnull(t0.B08_C2, 0)) AS rpt1_BS5,sum(ifnull(t0.B08_C2, 0)) AS rpt1_CF5,sum(CASE WHEN t0.DEPT_CLASS='D600' AND t0.CWSS_C11 IN (2,3,4) THEN ifnull(t0.B08_C2, 0) WHEN t0.DEPT_CLASS='D600' AND (t0.CWSS_C11 NOT IN (2,3,4) OR t0.CWSS_C11 IS NULL) THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_CK5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'D%' AND (t0.DEPT_CLASS<>'D600' OR t0.DEPT_CLASS IS NULL) AND (LEFT(t0.DEPT_CLASS, 4) IN ('D120') OR LEFT(t0.DEPT_CLASS, 4) IN ('D121') OR LEFT(t0.DEPT_CLASS, 4) IN ('D122') OR LEFT(t0.DEPT_CLASS, 4) IN ('D130') OR LEFT(t0.DEPT_CLASS, 4) IN ('D212') OR LEFT(t0.DEPT_CLASS, 4) IN ('D213') OR LEFT(t0.DEPT_CLASS, 4) IN ('D218') OR LEFT(t0.DEPT_CLASS, 4) IN ('D214') OR LEFT(t0.DEPT_CLASS, 4) IN ('D140') OR LEFT(t0.DEPT_CLASS, 4) IN ('D222') OR LEFT(t0.DEPT_CLASS, 4) IN ('D223')) THEN ifnull(t0.B08_C2, 0) WHEN t0.DEPT_CLASS LIKE 'D%' AND (t0.DEPT_CLASS<>'D600' OR t0.DEPT_CLASS IS NULL) AND (LEFT(t0.DEPT_CLASS, 4) NOT IN ('D120') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D121') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D122') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D130') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D212') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D213') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D218') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D214') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D140') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D222') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D223')) THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_CL5,sum(ifnull(t0.B08_C2, 0)) AS rpt1_CN5,sum(CASE WHEN t0.DEPT_CLASS='D600' AND t0.CWSS_C11 IN (2,3,4) THEN ifnull(t0.B08_C2, 0) WHEN t0.DEPT_CLASS='D600' AND (t0.CWSS_C11 NOT IN (2,3,4) OR t0.CWSS_C11 IS NULL) THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_CZ5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'D%' AND (t0.DEPT_CLASS<>'D600' OR t0.DEPT_CLASS IS NULL) AND (LEFT(t0.DEPT_CLASS, 4) IN ('D120') OR LEFT(t0.DEPT_CLASS, 4) IN ('D121') OR LEFT(t0.DEPT_CLASS, 4) IN ('D122') OR LEFT(t0.DEPT_CLASS, 4) IN ('D130') OR LEFT(t0.DEPT_CLASS, 4) IN ('D212') OR LEFT(t0.DEPT_CLASS, 4) IN ('D213') OR LEFT(t0.DEPT_CLASS, 4) IN ('D218') OR LEFT(t0.DEPT_CLASS, 4) IN ('D214') OR LEFT(t0.DEPT_CLASS, 4) IN ('D140') OR LEFT(t0.DEPT_CLASS, 4) IN ('D222') OR LEFT(t0.DEPT_CLASS, 4) IN ('D223')) THEN ifnull(t0.B08_C2, 0) WHEN t0.DEPT_CLASS LIKE 'D%' AND (t0.DEPT_CLASS<>'D600' OR t0.DEPT_CLASS IS NULL) AND (LEFT(t0.DEPT_CLASS, 4) NOT IN ('D120') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D121') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D122') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D130') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D212') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D213') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D218') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D214') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D140') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D222') AND LEFT(t0.DEPT_CLASS, 4) NOT IN ('D223')) THEN ifnull(t0.B08_TB4_C44, 0) ELSE 0 END) AS rpt1_DA5,sum(ifnull(t0.B08_C2, 0)) AS rpt1_DC5,0 AS rpt1_BW5,0 AS rpt1_BX5,0 AS rpt1_EM5,0 AS rpt1_DN5,0 AS rpt1_DO5,0 AS rpt1_DV5,0 AS rpt1_DW5,0 AS rpt1_ED5,0 AS rpt1_EE5,0 AS rpt1_EL5 FROM wstjfx.HZ_NB_YY t0 LEFT JOIN wstjfx.DIM_WTNB_JJLXDM t1 ON (t0.JJLX=t1.ID_) LEFT JOIN wstjfx.DIM_XZQH t2 ON (t0.DEPT_ADRRESSCODE=t2.ID AND (concat(LEFT(t0.BBQ_, 4), '1231')>=t2.fromdate AND concat(LEFT(t0.BBQ_, 4), '1231')<=t2.todate)) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND t0.BBQ_ LIKE '2024%') GROUP BY t0.DEPT_ADRRESSCODE UNION ALL SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t1.NAME) AS rpt1_A5_DSC, 0 AS rpt1_CT5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_CD5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_CR5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_AT5, 0 AS rpt1_AX5, 0 AS rpt1_BR5, 0 AS rpt1_BV5, 0 AS rpt1_CB5, 0 AS rpt1_CQ5, 0 AS rpt1_DF5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_AU5, 0 AS rpt1_AY5, 0 AS rpt1_CA5, 0 AS rpt1_CV5, 0 AS rpt1_DG5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_BS5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, sum(CASE WHEN t0.BBQ_ LIKE '2023%' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.YYDJ_J IN ('2','3') THEN ifnull(t0.B08_C13, 0) ELSE 0 END) AS rpt1_BW5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'B%' OR t0.DEPT_CLASS LIKE 'C2%' THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_BX5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) AND t0.BBQ_ LIKE '2021%' THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_EM5,sum(CASE WHEN t0.BBQ_ LIKE '2021%' AND (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C4, 0) ELSE 0 END) AS rpt1_DN5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) AND t0.BBQ_ LIKE '2021%' THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DO5,sum(CASE WHEN t0.BBQ_ LIKE '2021%' AND t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C4, 0) ELSE 0 END) AS rpt1_DV5,sum(CASE WHEN t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) AND t0.BBQ_ LIKE '2021%' THEN ifnull(t0.B08_C2, 0) ELSE 0 END) AS rpt1_DW5,sum(CASE WHEN t0.BBQ_ LIKE '2021%' AND (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C18, 0) ELSE 0 END) AS rpt1_ED5,sum(CASE WHEN (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) AND t0.BBQ_ LIKE '2021%' THEN ifnull(t0.B08_C12, 0) ELSE 0 END) AS rpt1_EE5,sum(CASE WHEN t0.BBQ_ LIKE '2021%' AND t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR (LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H'))) THEN ifnull(t0.B06_C18, 0) ELSE 0 END) AS rpt1_EL5 FROM wstjfx.HZ_NB_YY t0 LEFT JOIN wstjfx.DIM_XZQH t1 ON (t0.DEPT_ADRRESSCODE=t1.ID AND (concat(LEFT(t0.BBQ_, 4), '1231')>=t1.fromdate AND concat(LEFT(t0.BBQ_, 4), '1231')<=t1.todate)) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND (t0.BBQ_ LIKE '2023%' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.YYDJ_J IN ('2','3') OR t0.DEPT_CLASS LIKE 'B%' OR t0.DEPT_CLASS LIKE 'C2%' OR t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) AND t0.BBQ_ LIKE '2021%' OR t0.BBQ_ LIKE '2021%' AND (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) OR (t0.YYDJ_J<>'3' OR t0.YYDJ_J IS NULL) AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) AND t0.BBQ_ LIKE '2021%' OR t0.BBQ_ LIKE '2021%' AND t0.YYDJ_J='3' AND t0.JJLX IN ('11','12') AND (LEFT(t0.DEPT_CLASS, 2) IN ('A1') OR LEFT(t0.DEPT_CLASS, 2) IN ('A2') OR LEFT(t0.DEPT_CLASS, 2) IN ('A3') OR LEFT(t0.DEPT_CLASS, 2) IN ('A4') OR LEFT(t0.DEPT_CLASS, 2) IN ('A5') OR LEFT(t0.DEPT_CLASS, 3) IN ('A71') OR LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')))) GROUP BY t0.DEPT_ADRRESSCODE ) t0 GROUP BY t0.rpt1_A5 UNION ALL SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t1.NAME) AS rpt1_A5_DSC, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_AT5, 0 AS rpt1_AU5, 0 AS rpt1_AX5, 0 AS rpt1_AY5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_BR5, 0 AS rpt1_BS5, 0 AS rpt1_BV5, 0 AS rpt1_BW5, 0 AS rpt1_BX5, 0 AS rpt1_CA5, 0 AS rpt1_CB5, 0 AS rpt1_CD5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CQ5, 0 AS rpt1_CR5, 0 AS rpt1_CT5, 0 AS rpt1_CV5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, 0 AS rpt1_DF5, 0 AS rpt1_DG5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DN5, 0 AS rpt1_DO5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DV5, 0 AS rpt1_DW5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_ED5, 0 AS rpt1_EE5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EL5, 0 AS rpt1_EM5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, count(CASE WHEN t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A07%' OR t0.SZKSDM LIKE 'A08%' OR t0.SZKSDM LIKE 'A09%' OR t0.SZKSDM IN ('A50.04','A12.04')) THEN ifnull(t0.USERID_, '0') END) AS rpt1_H5,count(CASE WHEN t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A07%' OR t0.SZKSDM LIKE 'A08%' OR t0.SZKSDM LIKE 'A09%' OR t0.SZKSDM IN ('A50.04','A12.04')) THEN ifnull(t0.USERID_, '0') END) AS rpt1_S5,count(CASE WHEN t0.YSZYFWDMKXSG LIKE '%A14%' THEN ifnull(t0.USERID_, '0') END) AS rpt1_V5,count(CASE WHEN t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A15%' OR t0.SZKSDM LIKE 'B08%') THEN ifnull(t0.USERID_, '0') END) AS rpt1_Y5,count(CASE WHEN t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A15%' OR t0.SZKSDM LIKE 'B08%') THEN ifnull(t0.USERID_, '0') END) AS rpt1_AB5,count(CASE WHEN t0.YSZYFWDMKXSG LIKE '%A17%' THEN ifnull(t0.USERID_, '0') END) AS rpt1_AE5,0 AS rpt1_L5,0 AS rpt1_M5,0 AS rpt1_N5,0 AS rpt1_O5,0 AS rpt1_P5,0 AS rpt1_CC5,0 AS rpt1_CI5,0 AS rpt1_CJ5,0 AS rpt1_CS5,0 AS rpt1_CY5,0 AS rpt1_FB5,0 AS rpt1_FC5,0 AS rpt1_FJ5 FROM wstjfx.HZ_WSRL_OLAP_NB t0 LEFT JOIN wstjfx.DIM_XZQH t1 ON (t0.DEPT_ADRRESSCODE=t1.ID AND (date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')>=t1.fromdate AND date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')<=t1.todate)) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND t0.BBQ_ LIKE '2024%' AND (t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A07%' OR t0.SZKSDM LIKE 'A08%' OR t0.SZKSDM LIKE 'A09%' OR t0.SZKSDM IN ('A50.04','A12.04')) OR t0.YSZYFWDMKXSG LIKE '%A14%' OR t0.CSZYLBDM IN ('11','12') AND (t0.SZKSDM LIKE 'A15%' OR t0.SZKSDM LIKE 'B08%') OR t0.YSZYFWDMKXSG LIKE '%A17%')) AND (t0.BYRYLDQK IN ('11','12','13','14','19') OR t0.BYRYLDQK IS NULL OR t0.BYRYLDQK IN ('24','29') AND t0.BZQK='4') AND (t0.BBQ_ NOT LIKE '201711%') GROUP BY t0.DEPT_ADRRESSCODE UNION ALL SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t2.NAME) AS rpt1_A5_DSC, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_AT5, 0 AS rpt1_AU5, 0 AS rpt1_AX5, 0 AS rpt1_AY5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_BR5, 0 AS rpt1_BS5, 0 AS rpt1_BV5, 0 AS rpt1_BW5, 0 AS rpt1_BX5, 0 AS rpt1_CA5, 0 AS rpt1_CB5, 0 AS rpt1_CD5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CQ5, 0 AS rpt1_CR5, 0 AS rpt1_CT5, 0 AS rpt1_CV5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, 0 AS rpt1_DF5, 0 AS rpt1_DG5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DN5, 0 AS rpt1_DO5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DV5, 0 AS rpt1_DW5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_ED5, 0 AS rpt1_EE5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EL5, 0 AS rpt1_EM5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, 0 AS rpt1_H5, 0 AS rpt1_S5, 0 AS rpt1_V5, 0 AS rpt1_Y5, 0 AS rpt1_AB5, 0 AS rpt1_AE5, sum(ifnull(t1.ekys, 0)) AS rpt1_L5, sum(ifnull(t1.ekys1, 0)) AS rpt1_M5, sum(ifnull(t1.ekys2, 0)) AS rpt1_N5, sum(ifnull(t1.ekys4, 0)) AS rpt1_O5, sum(ifnull(t1.ekys3, 0)) AS rpt1_P5, 0 AS rpt1_CC5, 0 AS rpt1_CI5, 0 AS rpt1_CJ5, 0 AS rpt1_CS5, 0 AS rpt1_CY5, 0 AS rpt1_FB5, 0 AS rpt1_FC5, 0 AS rpt1_FJ5 FROM wstjfx.HZ_NB_YY t0 LEFT JOIN (SELECT a.USERID_, a.USERname_, a.BBQ_, a.DEPT_ADRRESSCODE, a.ZBDW, a.LSGX, a.JJLX, a.JGFLGL, a.YYDJ_D, a.YYDJ_J, a.DEPT_CLASS, a.B03_C3, a.B10_C10_FK, a.B10_C11_FK, a.B10_C12_FK, a.B10_C53, a.B10_E10_FK, a.B10_E11_FK, a.B10_E12_FK, a.B10_E53, a.B10_G10_FK, a.B10_G11_FK, a.B10_G12_FK, a.B10_G53, b.ekys, b.ekys1, b.ekys2, b.ekys3, b.ekys4, b.ekhs, b.ekhs1, b.ekhs2, b.ekhs3, b.ekhs4 FROM (SELECT * FROM HZ_NB_OLAP WHERE dept_class<>'A519') a LEFT JOIN (SELECT bbq_, USERID_, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A07' AND CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A08' AND CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys1, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A09' AND CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys2, SUM(CASE WHEN SZKSDM='A50.04' AND CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys3, SUM(CASE WHEN SZKSDM='A12.04' AND CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys4, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A07' AND CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A08' AND CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs1, SUM(CASE WHEN SUBSTR(SZKSDM,1,3) = 'A09' AND CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs2, SUM(CASE WHEN SZKSDM='A50.04' AND CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs3, SUM(CASE WHEN SZKSDM='A12.04' AND CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs4 FROM HZ_WSRL_ALL WHERE (BYRYLDQK IN ('11','12','13','14','19') OR BYRYLDQK is null OR (BYRYLDQK IN ('24','29') AND BZQK='4')) AND bbq_ IN ( SELECT DISTINCT max(BBQ_) OVER (partition by SUBSTR(BBQ_ , 1 ,4)) AS BBQ_MAX FROM HZ_WSRL_ALL UNION ALL SELECT '201801--' AS BBQ_MAX FROM dual ) AND dept_class<>'A519' AND bbq_!='201811--' GROUP BY userid_,bbq_ ) b ON SUBSTR(a.bbq_,1,4)=substr(b.bbq_,1,4)-1 AND a.userid_=b.userid_ UNION ALL SELECT a.USERID_, a.USERname_, a.BBQ_, a.DEPT_ADRRESSCODE, a.ZBDW, a.LSGX, a.JJLX, a.JGFLGL, a.YYDJ_D, a.YYDJ_J, a.DEPT_CLASS, a.B03_C3, a.B10_C10_FK, a.B10_C11_FK, a.B10_C12_FK, a.B10_C53, a.B10_E10_FK, a.B10_E11_FK, a.B10_E12_FK, a.B10_E53, a.B10_G10_FK, a.B10_G11_FK, a.B10_G12_FK, a.B10_G53, b.ekys, b.ekys1, b.ekys2, b.ekys3, b.ekys4, b.ekhs, b.ekhs1, b.ekhs2, b.ekhs3, b.ekhs4 FROM (SELECT USERID_, USERname_, BBQ_, DEPT_ADRRESSCODE, ZBDW, LSGX, JJLX, JGFLGL, YYDJ_D, YYDJ_J, DEPT_CLASS, B03_C3, (B03_C3) B10_C10_FK, 0 B10_C11_FK, 0 B10_C12_FK, 0 B10_C53, (B08_C2)B10_E10_FK, 0 B10_E11_FK, 0 B10_E12_FK, 0 B10_E53, (B08_C12) B10_G10_FK, 0 B10_G11_FK, 0 B10_G12_FK, 0 B10_G53 FROM HZ_NB_OLAP WHERE dept_class='A519') a LEFT JOIN ( SELECT bbq_, USERID_, SUM(CASE WHEN CSZYLBDM IN ('11','12') THEN 1 ELSE 0 END) AS ekys, 0 AS ekys1, 0 AS ekys2, 0 AS ekys3, 0 AS ekys4, SUM(CASE WHEN CSZYLBDM IN ('21','22') THEN 1 ELSE 0 END) AS ekhs, 0 AS ekhs1, 0 AS ekhs2, 0 AS ekhs3, 0 AS ekhs4 FROM HZ_WSRL_ALL WHERE (BYRYLDQK IN ('11','12','13','14','19') OR BYRYLDQK is null OR (BYRYLDQK IN ('24','29') AND BZQK='4')) AND bbq_ IN ( SELECT DISTINCT max(BBQ_) OVER (partition by SUBSTR(BBQ_ , 1 ,4)) AS BBQ_MAX FROM HZ_WSRL_ALL UNION ALL SELECT '201801--' AS BBQ_MAX FROM dual ) AND dept_class='A519' AND bbq_!='201811--' GROUP BY userid_,bbq_ ) b ON SUBSTR(a.bbq_,1,4)=substr(b.bbq_,1,4)-1 AND a.userid_=b.userid_) t1 ON (LEFT(t0.BBQ_, 4)=LEFT(t1.BBQ_, 4) AND t0.USERID_=t1.USERID_) LEFT JOIN wstjfx.DIM_XZQH t2 ON (t0.DEPT_ADRRESSCODE=t2.ID AND (concat(LEFT(t0.BBQ_, 4), '1231')>=t2.fromdate AND concat(LEFT(t0.BBQ_, 4), '1231')<=t2.todate)) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL)) GROUP BY t0.DEPT_ADRRESSCODE UNION ALL SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t2.NAME) AS rpt1_A5_DSC, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_AT5, 0 AS rpt1_AU5, 0 AS rpt1_AX5, 0 AS rpt1_AY5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_BR5, 0 AS rpt1_BS5, 0 AS rpt1_BV5, 0 AS rpt1_BW5, 0 AS rpt1_BX5, 0 AS rpt1_CA5, 0 AS rpt1_CB5, 0 AS rpt1_CD5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CQ5, 0 AS rpt1_CR5, 0 AS rpt1_CT5, 0 AS rpt1_CV5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, 0 AS rpt1_DF5, 0 AS rpt1_DG5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DN5, 0 AS rpt1_DO5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DV5, 0 AS rpt1_DW5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_ED5, 0 AS rpt1_EE5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EL5, 0 AS rpt1_EM5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, 0 AS rpt1_H5, 0 AS rpt1_S5, 0 AS rpt1_V5, 0 AS rpt1_Y5, 0 AS rpt1_AB5, 0 AS rpt1_AE5, 0 AS rpt1_L5, 0 AS rpt1_M5, 0 AS rpt1_N5, 0 AS rpt1_O5, 0 AS rpt1_P5, sum(CASE WHEN LEFT(t0.DEPT_CLASS, 2) NOT IN ('A2') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('A3') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('A4') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('M3') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('M4') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('D1') AND (t0.DEPT_CLASS NOT IN ('D212','D213','D214') OR t0.DEPT_CLASS IS NULL) THEN ifnull(t1.B10_E30, 0)+ifnull(t1.B10_E31, 0)+ifnull(t1.B10_E32, 0)+ifnull(t1.B10_E33, 0)+ifnull(t1.B10_E34, 0)+ifnull(t1.B10_E35, 0)+ifnull(t1.B10_E36, 0)+ifnull(t1.B10_E37, 0) ELSE 0 END) AS rpt1_CC5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' THEN ifnull(t0.B08_C2, 0) WHEN (LEFT(t0.DEPT_CLASS, 1) IN ('A') OR LEFT(t0.DEPT_CLASS, 1) IN ('G') OR LEFT(t0.DEPT_CLASS, 1) IN ('H')) AND t0.DEPT_CLASS NOT LIKE 'A8%' AND (t0.DEPT_CLASS NOT LIKE 'A2%' AND t0.DEPT_CLASS NOT LIKE 'A3%' AND t0.DEPT_CLASS NOT LIKE 'A4%') THEN ifnull(t1.ZYMZRC, 0) ELSE 0 END) AS rpt1_CI5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'C%' OR t0.DEPT_CLASS LIKE 'B%' THEN ifnull(t1.ZYMZRC, 0) ELSE 0 END) AS rpt1_CJ5,sum(CASE WHEN LEFT(t0.DEPT_CLASS, 2) NOT IN ('A2') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('A3') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('A4') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('M3') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('M4') AND LEFT(t0.DEPT_CLASS, 2) NOT IN ('D1') AND (t0.DEPT_CLASS NOT IN ('D212','D213','D214') OR t0.DEPT_CLASS IS NULL) AND (LEFT(t0.DEPT_CLASS, 1) IN ('B') OR LEFT(t0.DEPT_CLASS, 1) IN ('C') OR LEFT(t0.DEPT_CLASS, 1) IN ('D') OR t0.DEPT_CLASS='A720') THEN ifnull(t1.B10_E30, 0)+ifnull(t1.B10_E31, 0)+ifnull(t1.B10_E32, 0)+ifnull(t1.B10_E33, 0)+ifnull(t1.B10_E34, 0)+ifnull(t1.B10_E35, 0)+ifnull(t1.B10_E36, 0)+ifnull(t1.B10_E37, 0) ELSE 0 END) AS rpt1_CS5,sum(CASE WHEN t0.DEPT_CLASS LIKE 'C%' OR t0.DEPT_CLASS LIKE 'B%' THEN ifnull(t1.ZYMZRC, 0) ELSE 0 END) AS rpt1_CY5,0 AS rpt1_FB5,0 AS rpt1_FC5,0 AS rpt1_FJ5 FROM wstjfx.HZ_NB_YY t0 LEFT JOIN wstjfx.XNBFK t1 ON (t0.BBQ_=t1.YEAR AND t0.USERID_=t1.YLJGID) LEFT JOIN wstjfx.DIM_XZQH t2 ON (t0.DEPT_ADRRESSCODE=t2.ID AND (concat(LEFT(t0.BBQ_, 4), '1231')>=t2.fromdate AND concat(LEFT(t0.BBQ_, 4), '1231')<=t2.todate)) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND t0.BBQ_ LIKE '2024%') GROUP BY t0.DEPT_ADRRESSCODE UNION ALL SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, max(t3.NAME) AS rpt1_A5_DSC, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_AT5, 0 AS rpt1_AU5, 0 AS rpt1_AX5, 0 AS rpt1_AY5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_BR5, 0 AS rpt1_BS5, 0 AS rpt1_BV5, 0 AS rpt1_BW5, 0 AS rpt1_BX5, 0 AS rpt1_CA5, 0 AS rpt1_CB5, 0 AS rpt1_CD5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CQ5, 0 AS rpt1_CR5, 0 AS rpt1_CT5, 0 AS rpt1_CV5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, 0 AS rpt1_DF5, 0 AS rpt1_DG5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DN5, 0 AS rpt1_DO5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DV5, 0 AS rpt1_DW5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_ED5, 0 AS rpt1_EE5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EL5, 0 AS rpt1_EM5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, 0 AS rpt1_H5, 0 AS rpt1_S5, 0 AS rpt1_V5, 0 AS rpt1_Y5, 0 AS rpt1_AB5, 0 AS rpt1_AE5, 0 AS rpt1_L5, 0 AS rpt1_M5, 0 AS rpt1_N5, 0 AS rpt1_O5, 0 AS rpt1_P5, 0 AS rpt1_CC5, 0 AS rpt1_CI5, 0 AS rpt1_CJ5, 0 AS rpt1_CS5, 0 AS rpt1_CY5, count(CASE WHEN t0.SFSXTJYLJGJCHR='1' THEN ifnull(t1.rpt1_FB50, 0) END) AS rpt1_FB5,count(ifnull(t1.rpt1_FC50, 0)) AS rpt1_FC5,0 AS rpt1_FJ5 FROM wstjfx.N12_WTYB_HZ_OLAP t0 INNER JOIN (SELECT t0.DEPT_ADRRESSCODE AS rpt1_A5, count(CASE WHEN t0.SFSXTJYLJGJCHR='1' THEN ifnull(t0.USERID_, '0') END) AS rpt1_FB50,count(ifnull(t0.USERID_, '0')) AS rpt1_FC50,max(t1.NAME) AS rpt1_A5_DSC FROM wstjfx.N12_WTYB_HZ_OLAP t0 LEFT JOIN wstjfx.DIM_XZQH t1 ON (t0.DEPT_ADRRESSCODE=t1.ID AND (date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')>=t1.fromdate AND date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')<=t1.todate)) LEFT JOIN wstjfx.HZ_NB_YY t2 ON (t2.USERID_=t0.USERID_) LEFT JOIN wstjfx.DIM_WTNB_JJLXDM t3 ON (t0.JJLX=t3.ID_) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND t2.BBQ_ LIKE '2024%' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.FZJG='0' AND t0.YYDJ_J IN ('2','3') AND t3.TYP1_ID='6') AND (t0.BBQ_ LIKE '202211%') GROUP BY t0.DEPT_ADRRESSCODE ) t1 ON (t2.DEPT_ADRRESSCODE=t1.rpt1_A5) LEFT JOIN wstjfx.HZ_NB_YY t2 ON (t2.USERID_=t0.USERID_) LEFT JOIN wstjfx.DIM_XZQH t3 ON (t0.DEPT_ADRRESSCODE=t3.ID AND (date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')>=t3.fromdate AND date_format(last_day(str_to_date(concat(LEFT(t0.BBQ_, 6), '01'),'%Y%m%d')),'%Y%m%d')<=t3.todate)) LEFT JOIN wstjfx.DIM_WTNB_JJLXDM t4 ON (t0.JJLX=t4.ID_) WHERE (t0.DEPT_ADRRESSCODE LIKE '4401%' AND (t0.DEPT_ADRRESSCODE NOT IN ('440102','440107','440116','440183','440184') OR t0.DEPT_ADRRESSCODE IS NULL) AND t2.BBQ_ LIKE '2024%' AND (t0.DEPT_CLASS LIKE 'A1%' OR t0.DEPT_CLASS LIKE 'A2%' OR t0.DEPT_CLASS LIKE 'A3%' OR t0.DEPT_CLASS LIKE 'A4%' OR t0.DEPT_CLASS LIKE 'A5%' OR t0.DEPT_CLASS LIKE 'A71%') AND t0.FZJG='0' AND t0.YYDJ_J IN ('2','3') AND t4.TYP1_ID='6') AND (t0.BBQ_ LIKE '202211%') GROUP BY t0.DEPT_ADRRESSCODE UNION ALL SELECT t0.XZQH AS rpt1_A5, max(t1.NAME) AS rpt1_A5_DSC, 0 AS rpt1_B5, 0 AS rpt1_E5, 0 AS rpt1_AH5, 0 AS rpt1_AK5, 0 AS rpt1_AN5, 0 AS rpt1_AQ5, 0 AS rpt1_AT5, 0 AS rpt1_AU5, 0 AS rpt1_AX5, 0 AS rpt1_AY5, 0 AS rpt1_BB5, 0 AS rpt1_BC5, 0 AS rpt1_BF5, 0 AS rpt1_BG5, 0 AS rpt1_BJ5, 0 AS rpt1_BK5, 0 AS rpt1_BN5, 0 AS rpt1_BO5, 0 AS rpt1_BR5, 0 AS rpt1_BS5, 0 AS rpt1_BV5, 0 AS rpt1_BW5, 0 AS rpt1_BX5, 0 AS rpt1_CA5, 0 AS rpt1_CB5, 0 AS rpt1_CD5, 0 AS rpt1_CF5, 0 AS rpt1_CK5, 0 AS rpt1_CL5, 0 AS rpt1_CN5, 0 AS rpt1_CQ5, 0 AS rpt1_CR5, 0 AS rpt1_CT5, 0 AS rpt1_CV5, 0 AS rpt1_CZ5, 0 AS rpt1_DA5, 0 AS rpt1_DC5, 0 AS rpt1_DF5, 0 AS rpt1_DG5, 0 AS rpt1_DJ5, 0 AS rpt1_DK5, 0 AS rpt1_DN5, 0 AS rpt1_DO5, 0 AS rpt1_DQ5, 0 AS rpt1_DR5, 0 AS rpt1_DV5, 0 AS rpt1_DW5, 0 AS rpt1_DY5, 0 AS rpt1_DZ5, 0 AS rpt1_ED5, 0 AS rpt1_EE5, 0 AS rpt1_EG5, 0 AS rpt1_EH5, 0 AS rpt1_EL5, 0 AS rpt1_EM5, 0 AS rpt1_EO5, 0 AS rpt1_EP5, 0 AS rpt1_ET5, 0 AS rpt1_EU5, 0 AS rpt1_EX5, 0 AS rpt1_EY5, 0 AS rpt1_FF5, 0 AS rpt1_FG5, 0 AS rpt1_H5, 0 AS rpt1_S5, 0 AS rpt1_V5, 0 AS rpt1_Y5, 0 AS rpt1_AB5, 0 AS rpt1_AE5, 0 AS rpt1_L5, 0 AS rpt1_M5, 0 AS rpt1_N5, 0 AS rpt1_O5, 0 AS rpt1_P5, 0 AS rpt1_CC5, 0 AS rpt1_CI5, 0 AS rpt1_CJ5, 0 AS rpt1_CS5, 0 AS rpt1_CY5, 0 AS rpt1_FB5, 0 AS rpt1_FC5, sum(ifnull(t0.CZRK, '0')) AS rpt1_FJ5 FROM wstjfx.HZ_CZRK t0 LEFT JOIN wstjfx.DIM_XZQH t1 ON (t0.XZQH=t1.ID) WHERE (t0.XZQH LIKE '4401%' AND (t0.XZQH NOT IN ('440102','440107','440116','440183','440184') OR t0.XZQH IS NULL) AND t0.DQLX='QX' AND t0.BBQ_ LIKE '2021%') GROUP BY t0.XZQH ) t0 GROUP BY t0.rpt1_A5 ORDER BY t0.rpt1_A5 limit 100