[FSP]한달통계(1-15, 16-31, 1-31) SUM

한달통계(1일~15일 기준(first_half) 16일~31일 기준(second_half)), 한달sum 값 을 업체별로 조회한다.

 

WITH MM017 AS (
SELECT
  M017.LIFNR
        , M017.BUDAT
        , M017.INS_Q
        , M017.NETPR
FROM    ZMMT017 M017
WHERE   1=1
AND     M017.MANDT = #G_MANDT#
<isNotNull col="LIFNR">   
AND     M017.LIFNR = #LIFNR#
</isNotNull>
AND    SUBSTR(M017.BUDAT,1,6) = #BUDAT#
AND M017.PO_STATE IN ('56', '61')
)
SELECT
  LIFNR
        , SUM(CASE WHEN BUDAT >= '#BUDAT+#01' AND BUDAT <= '#BUDAT+#15' THEN (INS_Q * NETPR) ELSE 0 END)  AS FIRST_HALF
        , SUM(CASE WHEN BUDAT >= '#BUDAT+#16' AND BUDAT <= '#BUDAT+#31' THEN (INS_Q * NETPR) ELSE 0 END)  AS SECOND_HALF
        , SUM(INS_Q * NETPR) AS TOT
  , '' AS BIGO
FROM
        MM017
GROUP BY LIFNR
ORDER BY LIFNR