「月庫存SQL碼」:修訂間差異
| (未顯示同一使用者於中間所作的 2 次修訂) | |||
| 第1行: | 第1行: | ||
==程式碼== | ==程式碼== | ||
<sql> | <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 | 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' | ||
) | /*組合單單頭主件入庫數 正值*/ | ||
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>