「月庫存SQL碼」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 2 次修訂)
第1行: 第1行:
==程式碼==
==程式碼==
<sql>
<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'
(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
UNION ALL
/*銷退單  正值*/
/*銷退單  正值*/
第16行: 第15行:
UNION ALL
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'
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
UNION ALL
/*退貨單  負值*/
/*退貨單  負值*/
第23行: 第22行:
/*生產入庫單  正值*/
/*生產入庫單  正值*/
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
/*組合單單頭主件入庫數 正值*/
UNION ALL
SELECT TD001,TD002,'0001' AS TD003,TD004,TD007,TD012,TD003 FROM BOMTD WHERE TD012='Y'
/*組合單單身元件異動數 負值*/
UNION ALL
SELECT TE001,TE002,TE003,TE004,TE008*-1 AS TE008,TE010,TD003 FROM BOMTE JOIN BOMTD ON TE001=TD001 AND TE002=TD002 WHERE TE010='Y'
</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月10日 (四) 10:56 的最新修訂

程式碼

<sql> /*單別 單號 序號 品號 數量 確認碼 日期*/ 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' /*組合單單頭主件入庫數 正值*/ UNION ALL SELECT TD001,TD002,'0001' AS TD003,TD004,TD007,TD012,TD003 FROM BOMTD WHERE TD012='Y' /*組合單單身元件異動數 負值*/ UNION ALL SELECT TE001,TE002,TE003,TE004,TE008*-1 AS TE008,TE010,TD003 FROM BOMTE JOIN BOMTD ON TE001=TD001 AND TE002=TD002 WHERE TE010='Y' </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>