Forgot Password ??

PHP , ASP , ASP.NET, VB.NET, C#, Java , jQuery , Android , iOS , Windows Phone

	Registered : 109,037

HOME > .NET Framework > Forum > ถ้าต้องเขียน Sql Query ยาวมากๆๆ มีการ Joni หลาย Table ต้องใช้ ตัวแปรอะไรในการรับ นอกจาก String บ้างคะ

ถ้าต้องเขียน Sql Query ยาวมากๆๆ มีการ Joni หลาย Table ต้องใช้ ตัวแปรอะไรในการรับ นอกจาก String บ้างคะ

เริ่มหัวข้อใหม่

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

ถ้าต้องเขียน Sql Query ยาวมากๆๆ มีการ Joni หลาย Table ต้องใช้ ตัวแปรอะไรในการรับ นอกจาก String บ้างคะ

พอดีว่า เขียน Sql Query ยาวมากๆๆ เพราะเป็นการคำนวณด้วยเลยใส่สูตรคำนวณแล้ว Joni หลาย Table
แต่ตัวแปรที่ใช้รับเพื่อที่จะ Connect กับ database มันรับได้ไม่หมดนะคะ
มีวิธีไหนที่ช่วยให้สามารถ Run Sql query ยาวๆๆ ได้ไหมคะ

Tag : .NET, Ms SQL Server 2008, Win (Windows App), VS 2008 (.NET 3.x)

Date : 2012-12-26 10:10:44

By : golf_ng

View : 1112

Reply : 9

No. 1

ERO-TIC

โพสกระทู้ ( 61 )
บทความ ( 0 )

ตัวอย่างโค้ดเป็นยังไง

Date : 2012-12-26 10:40:32

By : ERO-TIC

No. 2

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

Code (VB.NET)

SELECT  Part.PartCode_JP, Part.PartCode_TH, Part.PartName ,STQTY = ISNULL(TBSTK.STKQTY,0) ,DLYQTY = ISNULL(TBDLY.DLYQTY,0)

/*===PCS_M 1 ===*/

       ,PCS_M1 = (ISNULL(TBORD01.ORD_01,0) + ISNULL(TBORD11.ORD_11,0) + ISNULL(TBORD21.ORD_21,0)  - ISNULL(TBMW1.MWQty1,0) - ISNULL(TBPW1.PWQty1,0))

/*===OTWQTY1===*/,OTWQTY1  = ISNULL(TBOTW1.OTWQty1,0)

/*===BALQTY1===*/,BALQTY1  = (ISNULL(TBSTK.STKQTY,0)

/*===BALQTY1===*/ -(IsNull(TBORD01.ORD_01, 0) + IsNull(TBORD11.ORD_11, 0) + IsNull(TBORD21.ORD_21, 0))  -(ISNULL(TBMW1.MWQty1,0) + ISNULL(TBPW1.PWQty1,0))  + ISNULL(TBOTW1.OTWQty1,0))

/*===PCS_M 2 ===*/

       ,PCS_M2 = (ISNULL(TBORD02.ORD_02,0) + ISNULL(TBORD12.ORD_12,0) + ISNULL(TBORD22.ORD_22,0)  - ISNULL(TBMW2.MWQty2,0) - ISNULL(TBPW2.PWQty2,0))

/*===OTWQTY2===*/,OTWQTY2  = ISNULL(TBOTW2.OTWQty2,0)

/*===BALQTY2===*/,BALQTY2  =

                              (ISNULL(TBSTK.STKQTY,0)

/*===BALQTY1===*/ -(IsNull(TBORD01.ORD_01, 0) + IsNull(TBORD11.ORD_11, 0) + IsNull(TBORD21.ORD_21, 0)) 

                          -(ISNULL(TBMW1.MWQty1,0) + ISNULL(TBPW1.PWQty1,0)) 

                          + ISNULL(TBOTW1.OTWQty1,0)

/*===BALQTY2===*/ -(IsNull(TBORD02.ORD_02, 0) + IsNull(TBORD12.ORD_12, 0) + IsNull(TBORD22.ORD_22, 0))  -(ISNULL(TBMW2.MWQty2,0) + ISNULL(TBPW2.PWQty2,0))  + ISNULL(TBOTW2.OTWQty2,0))

/*===PCS_M 3 ===*/

       ,PCS_M3 = (ISNULL(TBORD03.ORD_03,0) + ISNULL(TBORD13.ORD_13,0) + ISNULL(TBORD23.ORD_23,0)  - ISNULL(TBMW3.MWQty3,0) - ISNULL(TBPW3.PWQty3,0))

/*===OTWQTY3===*/,OTWQTY3  = ISNULL(TBOTW3.OTWQty3,0)

/*===BALQTY3===*/,BALQTY3  = (ISNULL(TBSTK.STKQTY,0)

/*===BALQTY1===*/ -(IsNull(TBORD01.ORD_01, 0) + IsNull(TBORD11.ORD_11, 0) + IsNull(TBORD21.ORD_21, 0))  -(ISNULL(TBMW1.MWQty1,0) + ISNULL(TBPW1.PWQty1,0))  + ISNULL(TBOTW1.OTWQty1,0))

/*===BALQTY2===*/ -(IsNull(TBORD02.ORD_02, 0) + IsNull(TBORD12.ORD_12, 0) + IsNull(TBORD22.ORD_22, 0))  -(ISNULL(TBMW2.MWQty2,0) + ISNULL(TBPW2.PWQty2,0))  + ISNULL(TBOTW2.OTWQty2,0)

/*===BALQTY3===*/ -(IsNull(TBORD03.ORD_03, 0) + IsNull(TBORD13.ORD_13, 0) + IsNull(TBORD23.ORD_23, 0))  -(ISNULL(TBMW3.MWQty3,0) + ISNULL(TBPW3.PWQty3,0))  + ISNULL(TBOTW3.OTWQty3,0)

/*===PCS_M 4 ===*/

       ,PCS_M4 = (ISNULL(TBORD04.ORD_04,0) + ISNULL(TBORD14.ORD_14,0) + ISNULL(TBORD24.ORD_24,0)  - ISNULL(TBMW4.MWQty4,0) - ISNULL(TBPW4.PWQty4,0))

/*===OTWQTY4===*/,OTWQTY4  = ISNULL(TBOTW4.OTWQty4,0)

/*===BALQTY4===*/,BALQTY4  = (ISNULL(TBSTK.STKQTY,0)

/*===BALQTY1===*/ -(IsNull(TBORD01.ORD_01, 0) + IsNull(TBORD11.ORD_11, 0) + IsNull(TBORD21.ORD_21, 0))  -(ISNULL(TBMW1.MWQty1,0) + ISNULL(TBPW1.PWQty1,0))  + ISNULL(TBOTW1.OTWQty1,0))

/*===BALQTY2===*/ -(IsNull(TBORD02.ORD_02, 0) + IsNull(TBORD12.ORD_12, 0) + IsNull(TBORD22.ORD_22, 0))  -(ISNULL(TBMW2.MWQty2,0) + ISNULL(TBPW2.PWQty2,0))  + ISNULL(TBOTW2.OTWQty2,0)

/*===BALQTY3===*/ -(IsNull(TBORD03.ORD_03, 0) + IsNull(TBORD13.ORD_13, 0) + IsNull(TBORD23.ORD_23, 0))  -(ISNULL(TBMW3.MWQty3,0) + ISNULL(TBPW3.PWQty3,0))  + ISNULL(TBOTW3.OTWQty3,0)

/*===BALQTY4===*/ -(IsNull(TBORD04.ORD_04, 0) + IsNull(TBORD14.ORD_14, 0) + IsNull(TBORD24.ORD_24, 0))  -(ISNULL(TBMW4.MWQty4,0) + ISNULL(TBPW4.PWQty4,0))  + ISNULL(TBOTW4.OTWQty4,0)

/*===PCS_M 5 ===*/

       ,PCS_M5 = (ISNULL(TBORD05.ORD_05,0) + ISNULL(TBORD15.ORD_15,0) + ISNULL(TBORD25.ORD_25,0)  - ISNULL(TBMW5.MWQty5,0) - ISNULL(TBPW5.PWQty5,0))

/*===OTWQTY5===*/,OTWQTY5  = ISNULL(TBOTW5.OTWQty5,0)

/*===BALQTY5===*/,BALQTY5  = (ISNULL(TBSTK.STKQTY,0)

/*===BALQTY1===*/ -(IsNull(TBORD01.ORD_01, 0) + IsNull(TBORD11.ORD_11, 0) + IsNull(TBORD21.ORD_21, 0))  -(ISNULL(TBMW1.MWQty1,0) + ISNULL(TBPW1.PWQty1,0))  + ISNULL(TBOTW1.OTWQty1,0)

/*===BALQTY2===*/ -(IsNull(TBORD02.ORD_02, 0) + IsNull(TBORD12.ORD_12, 0) + IsNull(TBORD22.ORD_22, 0))  -(ISNULL(TBMW2.MWQty2,0) + ISNULL(TBPW2.PWQty2,0))  + ISNULL(TBOTW2.OTWQty2,0)

/*===BALQTY3===*/ -(IsNull(TBORD03.ORD_03, 0) + IsNull(TBORD13.ORD_13, 0) + IsNull(TBORD23.ORD_23, 0))  -(ISNULL(TBMW3.MWQty3,0) + ISNULL(TBPW3.PWQty3,0))  + ISNULL(TBOTW3.OTWQty3,0)

/*===BALQTY4===*/ -(IsNull(TBORD04.ORD_04, 0) + IsNull(TBORD14.ORD_14, 0) + IsNull(TBORD24.ORD_24, 0))  -(ISNULL(TBMW4.MWQty4,0) + ISNULL(TBPW4.PWQty4,0))  + ISNULL(TBOTW4.OTWQty4,0)

/*===BALQTY5===*/ -(IsNull(TBORD05.ORD_05, 0) + IsNull(TBORD15.ORD_15, 0) + IsNull(TBORD25.ORD_25, 0))  -(ISNULL(TBMW5.MWQty5,0) + ISNULL(TBPW5.PWQty5,0))  + ISNULL(TBOTW5.OTWQty5,0))

,Part.Part_Type,PartSupplier.Supplier_ID

FROM PartSupplier INNER JOIN Part ON PartSupplier.PartCode_JP  = Part.PartCode_JP

INNER JOIN Supplier ON PartSupplier.Supplier_ID = Supplier.Supplier_ID

LEFT OUTER JOIN

        (

           SELECT SUM(STOCK.Qty) as  STKQTY, STOCK.PartCode_JP FROM STOCK GROUP BY  STOCK.PartCode_JP

        )

TBSTK

ON Part.PartCode_JP = TBSTK.PartCode_JP

LEFT OUTER JOIN

       (

           Select PODetail.PartCode_JP,DLYQTY = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND PODetail.Delivery  BETWEEN  '11/01/2012'  AND  '11/30/2012'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

       )

TBDLY

ON Part.PartCode_JP = TBDLY.PartCode_JP

 

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*                                     MONTH 1                                                         */

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*===================== [ 1.] CUSTOMER ORDER : custorder.order_type = '0' =====================*/

LEFT OUTER JOIN (

           SELECT SUM(BOM_Detail.Qty * CustOrder.Qty) AS ORD_01, BOM_Detail.PartCode_JP

           FROM CustOrder

           INNER JOIN BOM_Detail ON CustOrder.Code = BOM_Detail.Code AND  CustOrder.Rev_No = BOM_Detail.Rev_No

           INNER JOIN PartSupplier ON BOM_Detail.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date BETWEEN   '12/01/2012'  AND  '12/31/2012'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '0'

           GROUP BY BOM_Detail.PartCode_JP

           )

TBORD01

ON Part.PartCode_JP = TBORD01.PartCode_JP

 

/*===================== [ 1.] CUSTOMER ORDER : custorder.order_type = '1' =====================*/

LEFT OUTER JOIN

           (

               SELECT SUM(CustOrder.Qty) AS ORD_11 , Part.PartCode_JP

               FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

               INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

               WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

               AND  CustOrder.ship_Date  BETWEEN  '12/01/2012'  AND  '12/31/2012'

               AND (Custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

               AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

               AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND Custorder.order_type = '1'

           GROUP BY Part.PartCode_JP

           )

           TBORD11

           ON Part.PartCode_JP = TBORD11.PartCode_JP 

 

/*===================== [ 1.] CUSTOMER ORDER : custorder.order_type = '2' =====================*/

LEFT OUTER JOIN

           (

           SELECT SUM(CustOrder.Qty) AS ORD_21, Part.PartCode_JP

           FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

           INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date  BETWEEN  '12/01/2012'  AND  '12/31/2012'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '2'

           GROUP BY Part.PartCode_JP

           )

           TBORD21

           ON Part.PartCode_JP = TBORD21.PartCode_JP

 

/*===================== [ 1.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty1 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '12/01/2012'  AND  '12/31/2012'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW1

           ON Part.PartCode_JP = TBMW1.PartCode_JP

 

/*===================== [ 1.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty1

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '12/01/2012'  AND  '12/31/2012'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW1

           ON Part.PartCode_JP = TBPW1.PartCode_JP

 

/*===================== [ 1.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty1 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '12/01/2012'  AND  '12/31/2012'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW1

           ON Part.PartCode_JP = TBOTW1.PartCode_JP

 

 

 

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*                                     MONTH 2                                                         */

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

 

/*===================== [ 2.] CUSTOMER ORDER : custorder.order_type = '0' =====================*/

LEFT OUTER JOIN (

           SELECT SUM(BOM_Detail.Qty * CustOrder.Qty) AS ORD_02, BOM_Detail.PartCode_JP

           FROM CustOrder

           INNER JOIN BOM_Detail ON CustOrder.Code = BOM_Detail.Code AND  CustOrder.Rev_No = BOM_Detail.Rev_No

           INNER JOIN PartSupplier ON BOM_Detail.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date BETWEEN   '01/01/2013'  AND  '01/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '0'

           GROUP BY BOM_Detail.PartCode_JP

           )

TBORD02

ON Part.PartCode_JP = TBORD02.PartCode_JP

 

/*===================== [ 2.] CUSTOMER ORDER : custorder.order_type = '1' =====================*/

LEFT OUTER JOIN

           (

               SELECT SUM(CustOrder.Qty) AS ORD_12 , Part.PartCode_JP

               FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

               INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

               WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

               AND  CustOrder.ship_Date  BETWEEN  '01/01/2013'  AND  '01/31/2013'

               AND (Custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

               AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

               AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND Custorder.order_type = '1'

           GROUP BY Part.PartCode_JP

           )

           TBORD12

           ON Part.PartCode_JP = TBORD12.PartCode_JP 

 

/*===================== [ 2.] CUSTOMER ORDER : custorder.order_type = '2' =====================*/

LEFT OUTER JOIN

           (

           SELECT SUM(CustOrder.Qty) AS ORD_22, Part.PartCode_JP

           FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

           INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date  BETWEEN  '01/01/2013'  AND  '01/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '2'

           GROUP BY Part.PartCode_JP

           )

           TBORD22

           ON Part.PartCode_JP = TBORD22.PartCode_JP

 

/*===================== [ 2.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty2 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '01/01/2013'  AND  '01/31/2013'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW2

           ON Part.PartCode_JP = TBMW2.PartCode_JP

 

/*===================== [ 2.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty2

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '01/01/2013'  AND  '01/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW2

           ON Part.PartCode_JP = TBPW2.PartCode_JP

 

/*===================== [ 2.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty2 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '01/01/2013'  AND  '01/31/2013'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW2

           ON Part.PartCode_JP = TBOTW2.PartCode_JP

 

 

 

 

 

 

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*                                     MONTH 3                                                         */

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

 

       

/*===================== [ 3.] CUSTOMER ORDER : custorder.order_type = '0' =====================*/

LEFT OUTER JOIN (

           SELECT SUM(BOM_Detail.Qty * CustOrder.Qty) AS ORD_03, BOM_Detail.PartCode_JP

           FROM CustOrder

           INNER JOIN BOM_Detail ON CustOrder.Code = BOM_Detail.Code AND  CustOrder.Rev_No = BOM_Detail.Rev_No

           INNER JOIN PartSupplier ON BOM_Detail.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date BETWEEN   '03/01/2013'  AND  '03/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '0'

           GROUP BY BOM_Detail.PartCode_JP

           )

TBORD03

ON Part.PartCode_JP = TBORD03.PartCode_JP

 

/*===================== [ 3.] CUSTOMER ORDER : custorder.order_type = '1' =====================*/

LEFT OUTER JOIN

           (

               SELECT SUM(CustOrder.Qty) AS ORD_13 , Part.PartCode_JP

               FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

               INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

               WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

               AND  CustOrder.ship_Date  BETWEEN  '03/01/2013'  AND  '03/31/2013'

               AND (Custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

               AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

               AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND Custorder.order_type = '1'

           GROUP BY Part.PartCode_JP

           )

           TBORD13

           ON Part.PartCode_JP = TBORD13.PartCode_JP 

 

/*===================== [ 3.] CUSTOMER ORDER : custorder.order_type = '2' =====================*/

LEFT OUTER JOIN

           (

           SELECT SUM(CustOrder.Qty) AS ORD_23, Part.PartCode_JP

           FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

           INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date  BETWEEN  '03/01/2013'  AND  '03/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '2'

           GROUP BY Part.PartCode_JP

           )

           TBORD23

           ON Part.PartCode_JP = TBORD23.PartCode_JP

 

/*===================== [ 3.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty3 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '03/01/2013'  AND  '03/31/2013'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW3

           ON Part.PartCode_JP = TBMW3.PartCode_JP

 

/*===================== [ 3.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty3

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '03/01/2013'  AND  '03/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW3

           ON Part.PartCode_JP = TBPW3.PartCode_JP

 

/*===================== [ 3.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty3 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '03/01/2013'  AND  '03/31/2013'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW3

           ON Part.PartCode_JP = TBOTW3.PartCode_JP

 

 

 

 

 

 

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*                                     MONTH 4                                                         */

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

 

        

/*===================== [ 4.] CUSTOMER ORDER : custorder.order_type = '0' =====================*/

LEFT OUTER JOIN (

           SELECT SUM(BOM_Detail.Qty * CustOrder.Qty) AS ORD_04, BOM_Detail.PartCode_JP

           FROM CustOrder

           INNER JOIN BOM_Detail ON CustOrder.Code = BOM_Detail.Code AND  CustOrder.Rev_No = BOM_Detail.Rev_No

           INNER JOIN PartSupplier ON BOM_Detail.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date BETWEEN   '06/01/2013'  AND  '06/30/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '0'

           GROUP BY BOM_Detail.PartCode_JP

           )

TBORD04

ON Part.PartCode_JP = TBORD04.PartCode_JP

 

/*===================== [ 4.] CUSTOMER ORDER : custorder.order_type = '1' =====================*/

LEFT OUTER JOIN

           (

               SELECT SUM(CustOrder.Qty) AS ORD_14 , Part.PartCode_JP

               FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

               INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

               WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

               AND  CustOrder.ship_Date  BETWEEN  '06/01/2013'  AND  '06/30/2013'

               AND (Custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

               AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

               AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND Custorder.order_type = '1'

           GROUP BY Part.PartCode_JP

           )

           TBORD14

           ON Part.PartCode_JP = TBORD14.PartCode_JP 

 

/*===================== [ 4.] CUSTOMER ORDER : custorder.order_type = '2' =====================*/

LEFT OUTER JOIN

           (

           SELECT SUM(CustOrder.Qty) AS ORD_24, Part.PartCode_JP

           FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

           INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date  BETWEEN  '06/01/2013'  AND  '06/30/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '2'

           GROUP BY Part.PartCode_JP

           )

           TBORD24

           ON Part.PartCode_JP = TBORD24.PartCode_JP

 

/*===================== [ 4.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty4 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '06/01/2013'  AND  '06/30/2013'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW4

           ON Part.PartCode_JP = TBMW4.PartCode_JP

 

/*===================== [ 4.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty4

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '06/01/2013'  AND  '06/30/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW4

           ON Part.PartCode_JP = TBPW4.PartCode_JP

 

/*===================== [ 4.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty4 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '06/01/2013'  AND  '06/30/2013'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW4

           ON Part.PartCode_JP = TBOTW4.PartCode_JP

 

 

 

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

/*                                     MONTH 5                                                         */

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

 

 

 

 

/*===================== [ 5.] CUSTOMER ORDER : custorder.order_type = '0' =====================*/

LEFT OUTER JOIN (

           SELECT SUM(BOM_Detail.Qty * CustOrder.Qty) AS ORD_05, BOM_Detail.PartCode_JP

           FROM CustOrder

           INNER JOIN BOM_Detail ON CustOrder.Code = BOM_Detail.Code AND  CustOrder.Rev_No = BOM_Detail.Rev_No

           INNER JOIN PartSupplier ON BOM_Detail.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date BETWEEN   '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '0'

           GROUP BY BOM_Detail.PartCode_JP

           )

TBORD05

ON Part.PartCode_JP = TBORD05.PartCode_JP

 

/*===================== [ 5.] CUSTOMER ORDER : custorder.order_type = '1' =====================*/

LEFT OUTER JOIN

           (

               SELECT SUM(CustOrder.Qty) AS ORD_15 , Part.PartCode_JP

               FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

               INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

               WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

               AND  CustOrder.ship_Date  BETWEEN  '10/01/2013'  AND  '10/31/2013'

               AND (Custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

               AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

               AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND Custorder.order_type = '1'

           GROUP BY Part.PartCode_JP

           )

           TBORD15

           ON Part.PartCode_JP = TBORD15.PartCode_JP 

 

/*===================== [ 5.] CUSTOMER ORDER : custorder.order_type = '2' =====================*/

LEFT OUTER JOIN

           (

           SELECT SUM(CustOrder.Qty) AS ORD_25, Part.PartCode_JP

           FROM CustOrder INNER JOIN Part ON CustOrder.PartCode_JP = Part.PartCode_JP

           INNER JOIN PartSupplier ON Part.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Code NOT IN ( '02002','02003','02004','02005','02006','02007','02009','02011','02020','07850', '07851','07981','07982','08869','08999','09091','09092','09378','09833','09834', '09835','09836','09837','09838','09844','09845','09846','09939','10001','10073', '10074','10075','10076','01258','10555','10835','10836','10837','10838','10839', '10942','10943','10944','10945','10946','11419','11420','11484','11534','11609', '11843','11844','11924','11925','11926','11927','11928','11929','20005','20007', '20008','20009', '02000','02008','02014','02018','02021','02022','06788','07006','08126','08471', '09093','09377','10747','10903','11552','11591','20004','20006','20014',  '02004','02013','02017','02019','10680','10861','10863','10906','11420','11484', '11590','11599','11609','11844','11876','11927','11928','11929','20000','20001', '20002','20010','20011','20012','20013','20015','20016','20017','20018','20019','20020','20021','20022','20023')

           AND  CustOrder.ship_Date  BETWEEN  '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.Code IS NULL) AND (PartSupplier.Default_Sup = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0') AND custorder.order_type = '2'

           GROUP BY Part.PartCode_JP

           )

           TBORD25

           ON Part.PartCode_JP = TBORD25.PartCode_JP

 

/*===================== [ 5.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty5 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW5

           ON Part.PartCode_JP = TBMW5.PartCode_JP

 

/*===================== [ 5.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty5

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW5

           ON Part.PartCode_JP = TBPW5.PartCode_JP

 

/*===================== [ 5.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty5 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '10/01/2013'  AND  '10/31/2013'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW5

           ON Part.PartCode_JP = TBOTW5.PartCode_JP

 

 

 

WHERE Part.St_Part = '0'   AND (PartSupplier.Default_Sup = '1')

AND

           (

           (ISNULL(TBSTK.STKQTY,0) > 0)

           OR  (ISNULL(TBDLY.DLYQTY,0) > 0)

           OR  (ISNULL(TBORD01.ORD_01,0) + ISNULL(TBORD11.ORD_11,0) + ISNULL(TBORD21.ORD_21,0)            - ISNULL(TBMW1.MWQty1,0) - ISNULL(TBPW1.PWQty1,0) > 0)

           OR  (ISNULL(TBOTW1.OTWQty1,0) > 0)

           )

           ORDER BY   Part.PartCode_JP, PartSupplier.Supplier_ID

Sql Query ที่ใช้งานนะคะ

ส่วน Function ที่รับมาเพื่อออก ดึงข้อมูล

Code (VB.NET)

Public Shared Function GetTB(ByVal sSql As String, ByVal TBNM As String) As DataTable

        Dim DS As New DataSet(TBNM)

 

        Dim DA As SqlDataAdapter = New SqlDataAdapter(sSql, SdbStr)

        DA.Fill(DS, TBNM)

        If DS.Tables(TBNM).Rows.Count > 0 Then

            HaveDB = True

            Return DS.Tables(TBNM)

        Else

            HaveDB = False

            Return Nothing

        End If

    End Function

Date : 2012-12-26 10:48:58

By : golf_ng

No. 3

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

พอดีทำรายงานสรุปแบบ 5 เดือนน่ะ คะ ไม่รู้ว่า มันจะใช้ได้หรือเปล่า แต่ลองเขียนแล้ว Run ใน Sql Server 2008 แล้วใช้เวลาประมาณไม่ถึง 1 นาที แต่ถ้าเป็น Sql แบบหาเป็นช่วงๆๆ แล้วค่อยหยอดลง Table ใช้เวลานานกว่า แล้วมันเกิด Time Out ที่ เครื่องลูกค้าด้วย เลยลองแก้วิธีนี้ดูคะ
แต่ดันมาติดที่ Sql ที่ส่งให้

Code (VB.NET)

Dim DA As SqlDataAdapter = New SqlDataAdapter(sSql, SdbStr)

ลองเรียกมาดูปรากฏมันติดเหลือแค่ ของช่วงเดือน 5 นะคะ

Code (VB.NET)

/*===================== [ 5.] ADS : ModelWithdraw =====================*/

LEFT OUTER JOIN

           (

           SELECT ModelWith_Location.PartCode_Jp, SUM(ModelWith_Location.Qty) AS MWQty5 FROM ModelWith

           INNER JOIN ModelWith_Confirm ON ModelWith.MWith_No = ModelWith_Confirm.MWith_No

           INNER JOIN ModelWith_Location ON ModelWith_Confirm.ConfirmM_No = ModelWith_Location.ConfirmM_No

           AND ModelWith_Confirm.MWith_No = ModelWith_Location.MWith_No

           INNER JOIN CustOrder ON ModelWith.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON ModelWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date  BETWEEN  '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null OR custorder.supplier_id = '')  AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

          GROUP BY ModelWith_Location.PartCode_Jp, CustOrder.Complete,CustOrder.MDelete

           )

           TBMW5

           ON Part.PartCode_JP = TBMW5.PartCode_JP

 

/*===================== [ 5.] ADS : PartWithdraw (SaleOnly)  =====================*/

LEFT OUTER JOIN

           (

           SELECT PartWith_Location.PartCode_Jp, SUM(PartWith_Location.Qty) AS PWQty5

           FROM PartWith_Location INNER JOIN PartWith_Confirm ON PartWith_Location.PWith_No = PartWith_Confirm.PWith_No

           AND  PartWith_Location.ConfirmP_No = PartWith_Confirm.ConfirmP_No

           INNER JOIN CustOrder ON PartWith_Location.Order_No = CustOrder.Order_No

           LEFT OUTER JOIN PartSupplier ON PartWith_Location.PartCode_JP = PartSupplier.PartCode_JP

           WHERE CustOrder.Order_Type = '1'  AND (PartSupplier.Default_Sup = '1')

           AND CustOrder.ship_Date BETWEEN    '10/01/2013'  AND  '10/31/2013'

           AND (custorder.supplier_id is null or custorder.supplier_id = '') AND (CustOrder.Mstatus_no = '1')

           AND (CustOrder.MDelete = '0') AND (CustOrder.Complete = '0')

           GROUP BY CustOrder.Complete, CustOrder.MDelete, PartWith_Location.PartCode_Jp

           )

           TBPW5

           ON Part.PartCode_JP = TBPW5.PartCode_JP

 

/*===================== [ 5.] OTW QTY : DELAY PART  =====================*/

LEFT OUTER JOIN

           (

           Select PODetail.PartCode_JP,OTWQty5 = Sum(PODetail.Quantity) /*,Purchase.Po_Number, Purchase.Po_Date,PODetail.Delivery */

           From PODetail INNER JOIN Purchase ON PODetail.Po_Number = Purchase.Po_Number AND PODetail.Revised_Number = Purchase.Revised_Number

           INNER JOIN Part ON PODetail.PartCode_JP = Part.PartCode_JP

           Where  Purchase.Rev_Status = '1'  AND (PODetail.Complete <> '1')

           AND  PODetail.Delivery  BETWEEN    '10/01/2013'  AND  '10/31/2013'

           Group by PODetail.PartCode_JP /*,Part.PartCode_TH,Part.PartName,Purchase.Po_Number,Purchase.Po_Date,PODetail.Delivery, Part.Drawing_No ,Purchase.Remark */

           HAVING   Sum(PODetail.Quantity) > 0 

           )

           TBOTW5

           ON Part.PartCode_JP = TBOTW5.PartCode_JP

 

 

 

WHERE Part.St_Part = '0'   AND (PartSupplier.Default_Sup = '1')

AND

           (

           (ISNULL(TBSTK.STKQTY,0) > 0)

           OR  (ISNULL(TBDLY.DLYQTY,0) > 0)

           OR  (ISNULL(TBORD01.ORD_01,0) + ISNULL(TBORD11.ORD_11,0) + ISNULL(TBORD21.ORD_21,0)            - ISNULL(TBMW1.MWQty1,0) - ISNULL(TBPW1.PWQty1,0) > 0)

           OR  (ISNULL(TBOTW1.OTWQty1,0) > 0)

           )

           ORDER BY   Part.PartCode_JP, PartSupplier.Supplier_ID

เลยสงสัยว่ามันเกิดจากข้อจำกัดของ String หรือเปล่าอาจจะยาวไป

ถ้ามีวิธีอื่นที่ดีกว่าแนะนำด้วยนะคะ ขอบคุณมากคะ ^^

Date : 2012-12-26 10:54:17

By : golf_ng

No. 4

Guest

เห็นแล้วกลุ้ม

สร้าง view แล้วเรียก view มาใช้ได้เปล่า

Date : 2012-12-26 11:16:52

By : ห้ามตอบเกินวันละ 2 กระทู้

No. 5

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

View ตรงไหนคะ

Date : 2012-12-26 11:20:42

By : golf_ng

No. 6

Guest

สร้าง view ใน sql ก่อนไง

แล้วเรียก view มาใช้

Date : 2012-12-26 11:22:39

By : ห้ามตอบเกินวันละ 2 กระทู้

No. 7

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

ออ ได้คะ ใช้เวลาประมาณ 1 นาที แต่ติดตรงที่ส่ง Sql ให้ .net ยังไงคิดไม่ออกจริงๆๆ

พวก dataset ใช้ได้ไหมคะ

ลอง View แล้ว

ประวัติการแก้ไข
2012-12-26 11:54:04

Date : 2012-12-26 11:27:52

By : golf_ng

No. 8

Guest

งงกับ dataset

แค่สร้างเป็น view แล้ว save

สมมุติว่าชื่อ view1 ก็ใช้ได้แล้ว

เวลาใช้ก็ใช้แค่

select * from view1

จบเลย

Date : 2012-12-26 12:34:31

By : ห้ามตอบเกินวันละ 2 กระทู้

No. 9

golf

โพสกระทู้ ( 11 )
บทความ ( 0 )

ขอบคุณที่แนะนำคะ เดียวลองปรับใช้ดูคะ
ไม่เคยใช้ View มาก่อนนะคะ

ถ้าแบบนี้แสดงว่าเราก้อ สร้าง View เพือสร้างเป็น Table ใช่ไหมคะ
แล้วถ้าต้องกำหนดช่วงเดือนที่ต้องการดึงข้อมูลต้องกำหนดยังไงคะ
เพราะในแต่ละ Table ที่ Joni กัน ต้องส่งค่าช่วงเดือน แล้วก้อ Supplier Code ที่ต้องเลือกใช้ด้วยคะ

Date : 2012-12-26 14:22:08

By : golf_ng

ค้นหาข้อมูล

แสดงความคิดเห็น

Re : ถ้าต้องเขียน Sql Query ยาวมากๆๆ มีการ Joni หลาย Table ต้องใช้ ตัวแปรอะไรในการรับ นอกจาก String บ้างคะ

รายละเอียด

เพื่อความเรียบร้อยของข้อความ ควรจัดรูปแบบให้พอดีกับขนาดของหน้าจอ เพื่อง่ายต่อการอ่านและสบายตา และตรวจสอบภาษาไทยให้ถูกต้อง

อัพโหลดแทรกรูปภาพ

Notice

เพื่อความปลอดภัยของเว็บบอร์ด ไม่อนุญาติให้แทรก แท็ก [img]....[/img] โดยการอัพโหลดไฟล์รูปจากที่อื่น เช่นเว็บไซต์ ฟรีอัพโหลดต่าง ๆ
อัพโหลดแทรกรูปภาพ ให้ใช้บริการอัพโหลดไฟล์ของไทยครีเอท และตัดรูปภาพให้พอดีกับสกรีน เพื่อความโหลดเร็วและไฟล์ไม่ถูกลบทิ้ง

เพื่อความปลอดภัยและการตรวจสอบ กระทู้ที่แทรกไฟล์อัพโหลดไฟล์จากที่อื่น อาจจะถูกลบทิ้ง

โดย

อีเมล์

บวกค่าให้ถูก