SQL For WIP and DSN

User要求經過02站的帳，求其經過02站的數量，鑽孔數，pcs

SELECT A.PART_NUM,A.WIPQTY,B.DRILL_QTY,C.D1_SK,C.PIECES_IN_PANEL  FROM
(
SELECT PART_NUM,SUM(QTY_FINISHED) WIPQTY FROM CM_WIP_LOT_HISTORY A
WHERE A.ORGANIZATION_CODE = 'CC'
AND A.PROCESS_CODE = '02' 
AND A.OUT_PROCESS_TIME >='01-JAN-05'
AND A.OUT_PROCESS_TIME <='31-JAN-05'
GROUP BY PART_NUM ) A ,
(
 SELECT PART_NUM,SUM(DRILL_QUANTITY) DRILL_QTY
 FROM CM_BOM_PARTNUM_DRILL
 WHERE   PART_NUM >='A'
 AND     ( DRILL_SYM <>'T' OR DRILL_SYM IS NULL)
 AND     DRILL_SIZE NOT IN (4,5,6)
 AND     DRILL_QUANTITY <>99999
 AND     DEPARTMENT_CODE = '02'
 AND     ORGANIZATION_ID = 183
 ----AND     PART_NUM = 'U820037-1'
 GROUP BY PART_NUM ) B,
 (
SELECT A.PART_NUM,A.D1_SK,B.PIECES_IN_PANEL FROM CM_BOM_PARTNUM_PARAMETER A,CM_BOM_PARTNUM B
WHERE
    A.PART_NUM  = B.PART_NUM
AND A.ORGANIZATION_ID =  B.ORGANIZATION_ID
AND A.ORGANIZATION_ID = 183
--AND   PART_NUM = 'U820037-1'
) C
 WHERE  A.PART_NUM = B.PART_NUM
 AND    A.PART_NUM = C.PART_NUM
 

原文转自：http://www.ltesting.net