SELECT DISTINCT SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) AS BGT_TYPE, ( CASE SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ) WHEN "09" THEN "1" WHEN "10" THEN "2" WHEN "11" THEN "3" ELSE "9" END ) AS BGT_CLASS, SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) AS BGT_TEAM, ( ISNULL( (SELECT SUBSTRING(KMC101.KMC101_NAME,CHARINDEX ("-",KMC101.KMC101_NAME)+1,12) FROM KMC101 WHERE ( KMC101.KMC101_TYPE = "BUDGETCODE" ) AND ( KMC101.KMC101_CODE = SUBSTRING(KMM102.KMM102_BGT_TYPE,1,4 ) + "00" ) ),"" ) ) AS BGT_NAME, SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) AS BGT_AMT01, SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) AS BGT_AMT02, SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) AS BGT_AMT03, SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) AS BGT_AMT04, SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) AS BGT_AMT05, SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) AS BGT_AMT06, SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) AS BGT_AMT07, SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) AS BGT_AMT08, SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) AS BGT_AMT09, SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) AS BGT_AMT10, SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) AS BGT_AMT11, SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) AS BGT_AMT12, ( SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) ) AS AMT01, ( "XXXXXXXX" ) AS AMT02, SUM( ISNULL( ( CASE WHEN ( CONVERT(CHAR(8), KMM102.KMM102_CHK_DATE,112) < "YYYYMMDD" ) THEN KMM102.KMM102_CHK_AMT ELSE 0 END ),0 ) ) AS AMT09, SUM( ISNULL( ( CASE WHEN ( ( CONVERT(CHAR(8), KMM102.KMM102_OVER_DATE,112) < "YYYYMMDD" ) AND KMM102.KMM102_OVER_STATUS <> "00" ) THEN KMM102.KMM102_OVER_AMT WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < "YYYYMMDD" ) AND KMM102.KMM102_OVER_STATUS = "00" ) THEN KMM102.KMM102_OVER_AMT ELSE 0 END ),0 ) ) AS AMT07, SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" ) AND KMM103.KMM103_PAY_STATUS <> "2" ) THEN KMM103.KMM103_PAY_AMT ELSE 0 END FROM KMM103 WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY ) AND ( KMM103.KMM103_BGT_KIND =KMM102.KMM102_BGT_KIND ) AND ( KMM103.KMM103_CASE_NO =KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT03, SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < "YYYYMMDD" ) AND KMM103.KMM103_PAY_STATUS <> "1" ) THEN KMM103.KMM103_PAY_AMT ELSE 0 END FROM KMM103 WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY ) AND ( KMM103.KMM103_BGT_KIND =KMM102.KMM102_BGT_KIND ) AND ( KMM103.KMM103_CASE_NO =KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT04 FROM KMM102, KMM101 WHERE ( KMM102.KMM102_YY = "YYY" ) AND ( KMM102.KMM102_BGT_KIND = "1" ) AND ( KMM101.KMM101_YY =* KMM102.KMM102_YY ) AND ( KMM101.KMM101_BGT_KIND =* KMM102.KMM102_BGT_KIND ) AND ( KMM101.KMM101_BGT_TYPE =* KMM102.KMM102_BGT_TYPE )GROUP BY SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ), SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ), SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) ORDER BY BGT_CLASS ASC, BGT_TYPE DESC ;
