「月庫存SQL碼」:修訂間差異
| 第24行: | 第24行: | ||
SELECT TG001,TG002,TG003,TG004,TG011,TG022,TF003 FROM MOCTG JOIN MOCTF ON TG001=TF001 AND TG002=TF002 WHERE TG022='Y' | 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 | )X WHERE TH004='22100050' GROUP BY TH004 | ||
</sql> | |||
==上月庫存== | |||
<sql> | |||
SELECT MB001,MB002,SMC007-(THEIN+THEOUT) AS LMONTH,THEIN,THEOUT,SMC007 FROM (SELECT RTRIM(MB001) AS MB001,MB002,TH004, (SELECT CASE WHEN TH008>'0' THEN TH008 ELSE '0' END)AS THEIN,(SELECT CASE WHEN TH008<'0' THEN TH008 ELSE '0' END)AS THEOUT FROM | |||
(SELECT TH004,SUM(TH008) AS TH008 FROM v_ALLINOUT WHERE TH008< '0' AND TG003 BETWEEN '20151201' AND '20151209' GROUP BY TH004 | |||
UNION ALL | |||
SELECT TH004,SUM(TH008) AS TH008 FROM v_ALLINOUT WHERE TH008> '0' AND TG003 BETWEEN '20151201' AND '20151209' GROUP BY TH004)X | |||
JOIN INVMB ON TH004=MB001 | |||
WHERE MB007='303' OR MB001 LIKE '6%' | |||
)Y | |||
JOIN v_INVMC ON TH004=MC001 | |||
</sql> | </sql> | ||
於 2015年12月9日 (三) 17:24 的修訂
程式碼
<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' OR TB001='1401' OR TB001='1501' 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' OR TE001='5701' OR TE001='5901' 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>
上月庫存
<sql> SELECT MB001,MB002,SMC007-(THEIN+THEOUT) AS LMONTH,THEIN,THEOUT,SMC007 FROM (SELECT RTRIM(MB001) AS MB001,MB002,TH004, (SELECT CASE WHEN TH008>'0' THEN TH008 ELSE '0' END)AS THEIN,(SELECT CASE WHEN TH008<'0' THEN TH008 ELSE '0' END)AS THEOUT FROM (SELECT TH004,SUM(TH008) AS TH008 FROM v_ALLINOUT WHERE TH008< '0' AND TG003 BETWEEN '20151201' AND '20151209' GROUP BY TH004 UNION ALL SELECT TH004,SUM(TH008) AS TH008 FROM v_ALLINOUT WHERE TH008> '0' AND TG003 BETWEEN '20151201' AND '20151209' GROUP BY TH004)X JOIN INVMB ON TH004=MB001 WHERE MB007='303' OR MB001 LIKE '6%' )Y JOIN v_INVMC ON TH004=MC001 </sql>