Order status Report ?

Akhter HUssain

7y1.1kSQL

I want expected format which is given below with data in sql

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 #Sections (SecID INT,Secnam VARCHAR(50));
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);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');
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
(1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-06-2019',null)
, (3,11,1,002,200,'03-06-2019',null)
, (4,10,1,002,200,'08-06-2019',null)
, (4,1,1,003,200,'08-06-2019',null)
, (4,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);

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
Here is the updated version of your query
SELECT
    sales.OrderNo As OrderNo,
    Order_Ref_No AS Ref_No,
    ISNULL(SUM(Orderqty),0) AS OrderQty,
    ISNULL(SUM(Produce),0) AS Produce ,
    ISNULL(SUM(OrderQty) - SUM(Produce),0) AS Pending
FROM #SalesOrder sales
LEFT JOIN (
    SELECT OrderNo, SUM(prdQty) AS Produce
    FROM #Probale
    GROUP BY OrderNo
) probale ON sales.OrderNo = probale.OrderNo
LEFT JOIN (
    SELECT OrderNo,SUM(OrderQty) AS OrderQty
    FROM #OrderDetail
    GROUP BY OrderNo
) orders  ON sales.OrderNo = orders.OrderNo
GROUP BY sales.OrderNo,Order_Ref_No
The output it displays as below
Priyanka Jain7y ago
Updated Query as per your requirement.
Akhter HUssain7y ago
error is coming

Msg 195, Level 15, State 10, Procedure OrderStatus_report, Line 25
'CONCAT' is not a recognized built-in function name.
Priyanka Jain7y ago
As per Query you requested the query can form like this please try executing below query
DECLARE @qry AS VARCHAR(MAX),
@execQry AS VARCHAR(MAX)
SET @qry =' SELECT OrderNo,
        Ref_No,
        OrderQty,
        Produce,
        (OrderQty - Produce) AS Pending
FROM (
SELECT
    sales.OrderNo,
    Order_Ref_No AS Ref_No,
    SUM(Orderqty) AS OrderQty,
    SUM(prdQTY) AS Produce
FROM #SalesOrder sales
INNER JOIN #OrderDetail orders ON sales.OrderNo = orders.OrderNo
LEFT JOIN #Probale probale ON sales.Orderno  = probale.Orderno
                          AND orders.CodeItem = probale.CodeItem
GROUP BY sales.OrderNo, Order_Ref_No
) OrderSales '

SET @execQry = CONCAT(@qry,'UNION ALL ','SELECT NULL,''Total'',SUM(OrderQty),SUM(Produce), SUM(Pending) FROM (',@qry,') groupTotal')
EXEC(@execQry)
This will display output as below
Akhter HUssain7y ago
Pending qty will come from Probale qty will get minus to Orderdetail qty
Suraj Kumar7y ago
From which table Produce and Pending will come and what is its logic. By the way you can use the following query:
Select
    #SalesOrder.OrderNo,
    Order_Ref_No as Ref_No,
    SUM(Orderqty) as OrderQty
from #SalesOrder
inner join #OrderDetail on #SalesOrder.OrderNo = #OrderDetail.OrderNo
group by #SalesOrder.OrderNo, Order_Ref_No
It will give the output shown below:
OrderNo Ref_No OrderQty
--------------------------------------
1           A_01              10
--------------------------------------
2           B_01               9
--------------------------------------
3           C_01               9
--------------------------------------