Report req Sum of Sales order qty and daily produce quantity

Akhter HUssain

7y1.7kSQL

i want report in which sum of sales order qty of all (open) order and day wise produce qty require in a report here is my data and expected result...

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
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','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');
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);

here is my expected result...

9 Replies

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

Priyanka Jain7y ago
Accepted answer
You Query with updated total column
DECLARE @cols AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX),
    @sqlQuery AS NVARCHAR(MAX);

SET @sqlQuery = 'SELECT orders.DescriptionItem,orderQty,prdQty, EntryDate
                 FROM
                        (SELECT i.Descriptionitem, ISNULL(SUM(o.orderqty),0) orderqty
                         FROM #ItemMasterFile i
                         LEFT JOIN #OrderDetail o on (o.codeItem is NULL OR i.CodeItem = o.codeItem )
                         LEFT JOIN #SalesOrder s on  (s.orderno IS NULL OR o.OrderNo = s.OrderNo  )
                         WHERE s.Status IS NULL OR s.Status = ''Open''
                         GROUP BY Descriptionitem) as orders,
                        ( SELECT i.Descriptionitem,SUM(p.prdQty) as prdQty,p.EntryDate
                            FROM #ItemMasterFile i
                            LEFT JOIN #OrderDetail o on (o.codeItem is NULL OR i.CodeItem = o.codeItem )
                            LEFT JOIN #Probale p on  (p.OrderNo IS NULL OR o.orderNo = p.OrderNo ) AND o.CodeItem = p.CodeItem
                            GROUP BY Descriptionitem,p.EntryDate
                        ) as sales
                        WHERE orders.DescriptionItem = sales.DescriptionItem'

DECLARE @columns NVARCHAR(MAX),@sumcolumns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.EntryDate, 111), '/', '-'))
                FROM    #Probale AS p
                GROUP BY p.EntryDate
                ORDER BY p.EntryDate
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');

SET @sumcolumns = (SELECT  N', sum(p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.EntryDate, 111), '/', '-')) + ')'
                FROM    #Probale AS p
                GROUP BY p.EntryDate
                ORDER BY p.EntryDate
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');


                PRINT @sumcolumns
SET @sql = N'SELECT Descriptionitem , OrderQty, ' + STUFF(@columns, 1, 2, '') + ', Total = ISNULL(' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + ',0)
FROM
(
'+ @sqlQuery
+'
) AS j
PIVOT
(
  SUM(PrdQty) FOR EntryDate IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p

UNION ALL
SELECT ''Total'' , SUM(OrderQty), ' + STUFF(@sumcolumns, 1, 2, '') + ', SUM(ISNULL(' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + ',0))
FROM
(
'+ @sqlQuery
+'
) AS j
PIVOT
(
  SUM(PrdQty) FOR EntryDate IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p Order by DescriptionItem ;';
The output will be displayed as ,
+2
Priyanka Jain7y ago
It is because exec statement is missing u can add at last in query EXEC sp_executesql @sql;
+1
Akhter HUssain7y ago
Hi Priyanka ,

Last your provided query is executing but result is not showing...

0
Akhter HUssain7y ago
last thinkg row and column wise Total....
0
Priyanka Jain7y ago
yes noted and updated your query statement
+1
Akhter HUssain7y ago
Hi Priyanka Jain...

Now sum of order qty is coming correct,,but produce qty from probale table is coming row..
that there is not item is produced on Date 1-06-2019 except A item...
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);
your query is giving ouput that B item is produce on date 1-06-2019,which is wrong,bc there is no item B is produced.
0
Priyanka Jain7y ago
updated your query based on result provided
0
Akhter HUssain7y ago
Hi Priyanka Jain...

A Descritpion item is coming two time ,which should not come ..A orderqty is 9 and B qty is 6... please review
0
Priyanka Jain7y ago
You can try below query
DECLARE @cols AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX),
    @sqlQuery AS NVARCHAR(MAX);

SET @sqlQuery = 'SELECT orders.DescriptionItem,orderQty,prdQty, EntryDate
                 FROM
                        (SELECT i.Descriptionitem, ISNULL(SUM(o.orderqty),0) orderqty
                         FROM #ItemMasterFile i
                         LEFT JOIN #OrderDetail o on (o.codeItem is NULL OR i.CodeItem = o.codeItem )
                         LEFT JOIN #SalesOrder s on  (s.orderno IS NULL OR o.OrderNo = s.OrderNo  )
                         WHERE s.Status IS NULL OR s.Status = ''Open''
                         GROUP BY Descriptionitem) as orders,
                        ( SELECT i.Descriptionitem,SUM(p.prdQty) as prdQty,p.EntryDate
                            FROM #ItemMasterFile i
                            LEFT JOIN #OrderDetail o on (o.codeItem is NULL OR i.CodeItem = o.codeItem )
                            LEFT JOIN #SalesOrder s on (s.orderno is null OR o.OrderNo = s.OrderNo  )

LEFT JOIN #Probale p on (p.OrderNo IS NULL OR o.orderNo = p.OrderNo ) AND o.CodeItem = p.CodeItem
                            WHERE (s.Status IS NULL OR s.Status = ''Open'')
                            GROUP BY Descriptionitem,p.EntryDate
                        ) as sales
                        WHERE orders.DescriptionItem = sales.DescriptionItem'

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @Columns = (SELECT  N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.EntryDate, 111), '/', '-'))
                FROM    #Probale AS p
                GROUP BY p.EntryDate
                ORDER BY p.EntryDate
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)');

SET @sql = N'SELECT Descriptionitem , OrderQty, ' + STUFF(@columns, 1, 2, '') + ', Total = ISNULL(' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + ',0)
FROM
(
'+ @sqlQuery
+'
) AS j
PIVOT
(
  SUM(PrdQty) FOR EntryDate IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p  Order by DescriptionItem; ';


PRINT @sql;
EXEC sp_executesql @sql;
It will display output as below

Total is some fields is displaying incorrect result due to Null value once you resolved it you can check proper result

For dispalying null in place of 0 you can use any of the below links

https://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row

https://sqlhints.com/2015/05/30/how-to-replace-null-value-by-0-in-the-dynamic-pivot-result-sql-server/

https://www.c-sharpcorner.com/blogs/display-zero-instead-of-null-in-dynamic-pivot-in-sql-server1
+2