月庫存SQL碼

出自TSK WIKI
於 2015年12月9日 (三) 16:10 由 Root​(留言 | 貢獻) 所做的修訂 (→程式碼)
跳至導覽 跳至搜尋

程式碼

<sql> SELECT TH004,SUM(TH008)AS AMOUNT FROM /*銷貨單 負值*/ (SELECT TH001,TH002,TH003,TH004,TH008*-1 AS TH008,TH020,TG003 FROM COPTH JOIN COPTG ON TH001=TG001 AND TH002=TG002 WHERE TH020='Y' UNION ALL /*銷退單 正值*/ SELECT TJ001,TJ002,TJ003,TJ004,TJ007,TJ021,TI003 FROM COPTJ JOIN COPTI ON TJ001=TI001 AND TJ002=TI002 WHERE TI019='Y' UNION ALL /*進貨單 正值*/ SELECT TH001,TH002,TH003,TH004,TH007,TH030,TG014 FROM PURTH JOIN PURTG ON TH001=TG001 AND TH002=TG002 WHERE TH030='Y' UNION ALL /*異動 正負值*/ SELECT TB001,TB002,TB003,TB004,(SELECT CASE WHEN TB001='1102' OR TB001='1104' OR TB001='1105' OR TB001='1107' OR TB001='1199' THEN TB007 ELSE TB007*-1 END) TB007,TB018,TA003 FROM INVTB JOIN INVTA ON TB001=TA001 AND TB002=TA002 WHERE TB018='Y' UNION ALL /*領退料單 正負值*/ SELECT TE001,TE002,TE003,TE004,(SELECT CASE WHEN TE001='5601' OR TE001='5602' THEN TE005 ELSE TE005*-1 END) AS TE005,TE019,TC003 FROM MOCTE JOIN MOCTC ON TC001=TE001 AND TC002=TE002 WHERE TE019='Y' UNION ALL /*退貨單 負值*/ SELECT TJ001,TJ002,TJ003,TJ004,TJ009*-1 AS TJ009,TJ020,TI003 FROM PURTJ JOIN PURTI ON TJ001=TI001 AND TJ002=TI002 WHERE TJ020='Y' UNION ALL /*生產入庫單 正值*/ SELECT TG001,TG002,TG003,TG004,TG011,TG022,TF003 FROM MOCTG JOIN MOCTF ON TG001=TF001 AND TG002=TF002 WHERE TG022='Y' )X WHERE TH004='22100050' GROUP BY TH004 </sql>