누계 샘플

WITH CTE (PAY_DT, DEPO_CLS_CD,PAY_YM ,DETL_CTG_CD, PAY_CHR_CD,PAY_AMT ,SUMM)
AS(
 SELECT A.PAY_DT AS PAY_DT
     ,dbo. FN_FA_GET_CODE_NM('6016' ,MAX( B.DEPO_CLS_CD )) AS DEPO_CLS_CD
     , MAX (LEFT(B. PAY_YM,4 )) AS PAY_YM
     , dbo.FN_FA_GET_DETL_CTG_NM (A. ACCTUNIT_CD,MAX (B. ACCT_CTG_CD),MAX (B. DETL_CTG_CD)) AS DETL_CTG_CD
     , dbo .FN_FA_GET_ORG_NM( MAX(B .PAY_CHR_CD)) AS PAY_CHR_CD
     , SUM (B. PAY_AMT) AS PAY_AMT
     , MAX (B. SUMM) AS SUMM
  FROM TB_FA_DONA_PAYM_M A
INNER JOIN TB_FA_DONA_PAYM_D B
ON A .ACCTUNIT_CD = B .ACCTUNIT_CD
AND A .DONA_PAY_NO = B .DONA_PAY_NO
GROUP BY A. ACCTUNIT_CD, A.PAY_DT , B .DONA_PAY_SEQ  --WITH ROLLUP
)
SELECT PAY_DT , PAY_DT AS SORT_NO1,DEPO_CLS_CD ,PAY_YM, DETL_CTG_CD,PAY_CHR_CD ,PAY_AMT, SUMM , '1' AS SORT_NO2
FROM CTE
UNION ALL
SELECT ' 일계', PAY_DT AS SORT_NO1, '', '', '', '', SUM( PAY_AMT) ,''  ,'2' AS SORT_NO2
FROM CTE
GROUP BY PAY_DT
UNION ALL
SELECT ' 월계',LEFT( PAY_DT,6 )+'88' AS SORT_NO1 , '' , '' , '' , '' , SUM(PAY_AMT ) , ''  , '3' AS SORT_NO2
FROM CTE
GROUP BY LEFT(PAY_DT ,6)
UNION ALL
SELECT ' 월누계', C.PAY_DT +'99' AS SORT_NO1 , '' , '' , '' , '' , SUM (D. PAY_AMT) ,''  ,'4' AS SORT_NO2
FROM (SELECT MAX(LEFT( PAY_DT,6 )) AS PAY_DT, SUM(PAY_AMT ) AS PAY_AMT FROM CTE GROUP BY LEFT(PAY_DT, 6) ) AS C
CROSS JOIN (SELECT MAX(LEFT( PAY_DT,6 )) AS PAY_DT, SUM(PAY_AMT ) AS PAY_AMT FROM CTE GROUP BY LEFT( PAY_DT,6 )) AS D
WHERE C .PAY_DT >= D .PAY_DT
GROUP BY C. PAY_DT
UNION ALL
SELECT ' 총계', '99999999' AS SORT_NO1, '', '', '', '', SUM( PAY_AMT) ,''  ,'5' AS SORT_NO2
FROM CTE
ORDER BY SORT_NO1, SORT_NO2