Add Dispatch Table Qty in query

Question

Below is data

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,);  CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);  CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);  CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);  -- Pack Table  Create TABLE #tbl_BalPacM (PID int,Orderno int,P_Date date)  Create TABLE #tbl_PackD (PDID int,Codeitem int,PID int,P_QTY int,BID int )  -- Dispatch Table  Create TABLE #SB_Dispatch_M (ID int,Orderno int,S_Date date)  Create TABLE #SB_Dispatch_D (DID int,PID int)  INSERT INTO #ItemMasterFile VALUES    (1,'A',1,100)  , (2,'B',2,100)  , (3,'C',3,100)  , (4,'D',4,100)  , (5,'e',5,100)  , (6,'f',6,100)  , (7,'g',4,100)  , (8,'h',4,100)  , (9,'K',2,100)  , (10,'L',4,100)  , (11,'M',2,100);  INSERT INTO #Probale VALUES    (1111,1,1,001,100,'01-06-2019',null)  , (1112,3,1,001,200,'02-06-2019',null)  , (1113,11,1,002,200,'03-06-2019',null)  , (1114,10,1,002,200,'08-06-2019',null)  , (1115,1,1,003,200,'08-06-2019',null)  , (1116,3,1,003,200,'08-06-2019',null);  INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019')  , (002,'B_01','01-05-2019')  , (003,'C_01','01-05-2019');  INSERT INTO #OrderDetail VALUES    (1,001,1,5)  , (2,001,2,3)  , (3,001,3,2)  , (4,002,10,4)  , (5,002,11,3)  , (6,002,3,2)  , (7,003,1,4)  , (8,003,2,3)  , (9,003,3,2);  --Pack Table  INSERT INTO #tbl_BalPacM values(1001,001,'2023-06-01')  insert into #tbl_PackD values (1,1,1001,1,1111)  insert into #tbl_PackD values (2,3,1001,3,1112)  --Dispatch Table  Insert into #SB_Dispatch_M values(1,001,'2023-06-01')  insert into #SB_Dispatch_D values(11,1001)  SELECT     sales.OrderNo, sales.Order_Ref_No  , ISNULL(SUM(orders.OrderQty), 0) AS OrderQty, ISNULL(SUM(probale.Produce), 0) AS Produce,                         ISNULL(SUM(orders.OrderQty) - SUM(probale.Produce), 0) AS Pending  FROM         #SalesOrder AS sales LEFT OUTER JOIN                        (SELECT     Probale_1.OrderNo, SUM(prdqty) AS Produce                              FROM          #Probale AS Probale_1   							                              WHERE      (DelID IS NULL)                               GROUP BY Probale_1.OrderNo) AS probale ON sales.OrderNo = probale.OrderNo LEFT OUTER JOIN                            (SELECT     OrderNO, SUM(orderqty) AS OrderQty                              FROM          #OrderDetail  							                              GROUP BY OrderNO) AS orders ON sales.OrderNo = orders.OrderNO  GROUP BY sales.OrderNo, sales.Order_Ref_No  

I wanna add dispatch table qty column as below

Dispatch table qty is sum of Pack table ,which ID is input into dispatch table

Answer 1

Hi @Analyst_SQL

How about this query:

SELECT sales.OrderNo
      ,sales.Order_Ref_No
	  ,ISNULL(SUM(orders.OrderQty), 0) AS OrderQty
	  ,ISNULL(SUM(probale.Produce), 0) AS Produce
	  ,ISNULL(SUM(orders.OrderQty) - SUM(probale.Produce), 0) AS Pending  
	  ,ISNULL(SUM(dispatch_qty), 0) AS Dispatch
FROM #SalesOrder AS sales 
LEFT OUTER JOIN 
    (SELECT Probale_1.OrderNo, SUM(prdqty) AS Produce 
	 FROM #Probale AS Probale_1 
	 WHERE (DelID IS NULL) 
	 GROUP BY Probale_1.OrderNo) AS probale 
ON sales.OrderNo = probale.OrderNo 
LEFT OUTER JOIN
    (SELECT OrderNO, SUM(orderqty) AS OrderQty 
     FROM #OrderDetail
     GROUP BY OrderNO) AS orders 
ON sales.OrderNo = orders.OrderNO  
LEFT OUTER JOIN
    (SELECT B.Orderno,B.PID,COUNT(DISTINCT P.PDID)AS dispatch_qty
     FROM #tbl_BalPacM B 
	   JOIN #tbl_PackD P ON B.PID=P.PID
	   JOIN #SB_Dispatch_D D ON B.PID = D.PID
     GROUP BY OrderNO,B.PID) AS Dispatch 
ON sales.OrderNo = Dispatch.OrderNO 
GROUP BY sales.OrderNo, sales.Order_Ref_No

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

SELECT
    sales.OrderNo,
    sales.Order_Ref_No,
    ISNULL(SUM(orders.OrderQty), 0) AS OrderQty,
    ISNULL(SUM(probale.Produce), 0) AS Produce,
    ISNULL(SUM(pack.P_QTY), 0) AS DispatchQty,
    ISNULL(SUM(orders.OrderQty) - SUM(probale.Produce), 0) AS Pending
FROM
    #SalesOrder AS sales
LEFT OUTER JOIN
    (
    SELECT
        Probale_1.OrderNo,
        SUM(prdqty) AS Produce
    FROM
        #Probale AS Probale_1
    WHERE
        (DelID IS NULL)
    GROUP BY
        Probale_1.OrderNo
    ) AS probale ON sales.OrderNo = probale.OrderNo
LEFT OUTER JOIN
    (
    SELECT
        tbl_PackD.PID,
        SUM(tbl_PackD.P_QTY) AS P_QTY
    FROM
        #tbl_PackD
    INNER JOIN
        #SB_Dispatch_D ON #tbl_PackD.PDID = #SB_Dispatch_D.DID
    GROUP BY
        tbl_PackD.PID
    ) AS pack ON sales.OrderNo = pack.PID
LEFT OUTER JOIN
    (
    SELECT
        OrderNO,
        SUM(orderqty) AS OrderQty
    FROM
        #OrderDetail
    GROUP BY
        OrderNO
    ) AS orders ON sales.OrderNo = orders.OrderNO
GROUP BY
    sales.OrderNo,
    sales.Order_Ref_No;

Try this query this includes an additional join with #SB_Dispatch_D table to retrieve the relevant Pack table IDs that are present in the Dispatch table. The sum of P_QTY from the Pack table is then calculated for those matching IDs using a join with #tbl_PackD. The resulting sum is assigned to the DispatchQty column in the select statement.