W_KMR01_11 ---- KMM13BR COPYWRITE BY LD 2000/09/07 15:30
? 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?
;
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
