電線使用明細

出自TSK WIKI
跳至導覽 跳至搜尋

查詢程式碼

<sql> SELECT * FROM ( SELECT XX.*,ROUND((A1+A2+A3+A13+A14+A15)/6,0) AS SAVG,SMC007,X.UDF06 AS PRESA,ROUND(UNUSE,0)AS UNUSE,ROUND((SMC007-X.UDF06-UNUSE),0) AS PREALL,QTA, ROUND((SMC007-X.UDF06-UNUSE-(SELECT CASE WHEN QTA<0 THEN QTA ELSE 0 END)),0) AS FAMOUNT, XX.MB001 AS TA006 FROM ( SELECT MB001, ROUND([202012],0) AS A15,ROUND([202101],0) AS A14,ROUND([202102],0) AS A13,[202103] AS A3,[202104] AS A2,[202105] AS A1,[202106] AS A0 FROM ( SELECT RTRIM(MB001) MB001,THEOUT,THEMONTH FROM CABLEINOUT WHERE (THEMONTH BETWEEN '202103' AND '202106' OR THEMONTH BETWEEN '202012' AND '202102' ) AND MB001<>'TSS-089-3C大同' ) AS p PIVOT ( SUM(THEOUT) FOR THEMONTH IN ([202012],[202101],[202102],[202103], [202104],[202105],[202106]) ) AS pt1 ) XX LEFT JOIN v_INVMC01 ON MC001=MB001 JOIN INVMB X ON X.MB001=XX.MB001 LEFT JOIN (SELECT TA006,(SUM(TA017)-SUM(TA015))AS QTA FROM MOCTA WHERE TA011<>'Y' AND TA011<>'y' AND TA013<>'V' GROUP BY TA006)Z ON XX.MB001=TA006 LEFT JOIN (SELECT TB003,SUM(TB004-TB005)AS UNUSE FROM MOCTB JOIN MOCTA ON TA001=TB001 AND TA002=TB002 WHERE TB018='Y' AND TA011<>'Y' AND TA011<>'y' GROUP BY TB003)ZZ ON XX.MB001=TB003 )YY </sql>

匯入程式碼

  • 網址 php/CABLEINOUT.php?q=202104
  • 程式碼

<sql> INSERT INTO CABLEINOUT SELECT RTRIM(MB001) MB001,MB002,LMONTH,THEIN,THEOUT,HMOUT,SOLD,SMC007,'202104' THEMONTH FROM INVMB JOIN ( SELECT TH004,SUM(ASUM) LMONTH,SUM(AIN) THEIN,SUM(HMOUT+SAOUT) THEOUT, SUM(HMOUT)HMOUT, SUM(SAOUT) SOLD,SUM(ASUM+AIN-HMOUT-SAOUT) SMC007 FROM( SELECT TH004,SUM(CASE WHEN TH001='1201' AND TH007='06' THEN TH008*-1.0 ELSE TH008 END) ASUM,0 AIN, 0 HMOUT, 0 SAOUT FROM v_ALLINOUT WHERE TH020='Y' AND (TH007='01' OR (TH001='1201' AND TH007='06')) AND TG003 < 20210401 GROUP BY TH004

UNION ALL SELECT TH004,0 ASUM,SUM(CASE WHEN TH001='1201' AND TH007='06' THEN TH008*-1.0 WHEN TH008>=0 THEN TH008 ELSE 0 END) AIN, 0 HMOUT, 0 SAOUT FROM v_ALLINOUT WHERE TH020='Y' AND (TH001<>'1201' OR (TH001='1111' AND TH007='01') OR (TH001='1201' AND TH007='06')) AND TG003 LIKE '202104%' GROUP BY TH004

UNION ALL SELECT TH004,0 ASUM,0 AIN, 0 HMOUT, SUM(TH008*-1.0) SAOUT FROM v_ALLINOUT WHERE TH020='Y' AND TH001<>'1201' AND (TH001='2301' OR TH001='2401') AND TG003 LIKE '202104%' GROUP BY TH004

UNION ALL SELECT TH004,0 ASUM,0 AIN, SUM(TH008*-1.0) HMOUT, 0 SAOUT FROM v_ALLINOUT WHERE TH020='Y' AND TH001='1201' AND TH007='06' AND TG003 LIKE '202104%' GROUP BY TH004 )X GROUP BY TH004 )Y ON MB001=TH004 AND MB007='302' WHERE MB001 NOT LIKE'6%' AND MB001 NOT LIKE '2%' AND MB001 NOT LIKE '4%' ORDER BY MB001 </sql>