I want Produce Probale QTY order reference wise ..

i want report which will display that how many production is done against order ,and also display pending qty ,i attachment report format below with data....please guide

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);
CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
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);

As per your query , i have prepared a query please check this and based on your required output you can modify it accordingly

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@sqlQuery AS NVARCHAR(MAX);
SET @SqlQuery = 'SELECT s.Order_Ref_No,p.Entrydate,COUNT(p.prdQTY) AS ProductQty
FROM #ItemMasterFile i
INNER JOIN #Probale p ON i.CodeItem = p.CodeItem
INNER JOIN #OrderDetail o on p.Orderno=o.OrderNO
INNER JOIN #SalesOrder s on s.OrderNo = o.OrderNO
GROUP BY s.Order_Ref_No,p.Entrydate';
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.Order_Ref_No)
FROM (SELECT s.Order_Ref_No FROM
#SalesOrder s GROUP BY s.Order_Ref_No) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT ' + @cols + ',Entrydate FROM
(
'+@sqlQuery+'
) x
pivot
(
COUNT(ProductQty)
for Order_Ref_No in (' + @cols + ')
) p '
EXECUTE(@query)

This will display output as below

1 Reply

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

Priyanka Jain7y ago
Accepted answer
As per your query , i have prepared a query please check this and based on your required output you can modify it accordingly
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @sqlQuery AS NVARCHAR(MAX);

SET @SqlQuery = 'SELECT s.Order_Ref_No,p.Entrydate,COUNT(p.prdQTY) AS ProductQty
                 FROM #ItemMasterFile i
                 INNER JOIN #Probale p ON i.CodeItem = p.CodeItem
                 INNER JOIN #OrderDetail o on p.Orderno=o.OrderNO
                 INNER JOIN #SalesOrder s on s.OrderNo = o.OrderNO
                 GROUP BY s.Order_Ref_No,p.Entrydate';

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.Order_Ref_No)
            FROM (SELECT s.Order_Ref_No FROM
                         #SalesOrder s GROUP BY s.Order_Ref_No) c
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

SET @query = 'SELECT ' + @cols + ',Entrydate FROM
            (
                '+@sqlQuery+'
           ) x
            pivot
            (
                 COUNT(ProductQty)
                for Order_Ref_No in (' + @cols + ')
            ) p '

EXECUTE(@query)
This will display output as below