計劃實際表 對比兩個計劃笨蚁、實際差異
SELECT A.MATNR,SUM(isnull(C.mengeb,0)) - sum(ISNULL(B.mengeb,0)),N'1'AS FENLEI FROM #T_PP12T_3 A
left join(SELECT PP20.MATNR ,ISNULL(sum(menge),0) as mengeb from PP20T PP20 INNER JOIN #T_PP12T_3 PP12 ON PP20.MATNR = PP12.MATNR where GSTRS < @S_dtpWRKDA and KZEAR = 'N' group by PP20.MATNR) C
ON a.MATNR = c.MATNR
left join (SELECT PP21.MATNR,ISNULL(sum(ISNULL(GDMNG,0) + ISNULL( BLMNG,0)),0) as mengeb from PP21T PP21 INNER JOIN #T_PP12T_3 PP12 ON PP21.MATNR = PP12.MATNR where GSTRS < @S_dtpWRKDA AND NOT EXISTS (SELECT AUFNR FROM dbo.PP20T WHERE AUFNR = PP21.AUFNR AND KZEAR ='Y') group by PP21.MATNR ) B
ON A.MATNR = B.MATNR
GROUP BY A.MATNR
UNION
SELECT A.MATNR,SUM(isnull(C.mengeb,0)) - sum(ISNULL(B.mengeb,0)),N'2'AS FENLEI FROM #T_PP12T_3 A
left join(SELECT PP20.MATNR ,ISNULL(sum(menge),0) as mengeb from PP20T PP20 INNER JOIN #T_PP12T_3 PP12 ON PP20.MATNR = PP12.MATNR where GSTRS < @S_dtpWRKDA2 and KZEAR = 'N' group by PP20.MATNR) C
ON a.MATNR = c.MATNR
left join (SELECT PP21.MATNR,ISNULL(sum(ISNULL(GDMNG,0) + ISNULL( BLMNG,0)),0) as mengeb from PP21T PP21 INNER JOIN #T_PP12T_3 PP12 ON PP21.MATNR = PP12.MATNR where GSTRS < @S_dtpWRKDA2 AND NOT EXISTS(SELECT AUFNR FROM DBO.PP20T WHERE AUFNR = PP21.AUFNR AND KZEAR = 'Y') group by PP21.MATNR ) B
ON A.MATNR = B.MATNR
GROUP BY A.MATNR
