Output requiren using sql query

Question

Below is data

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50));  
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 #Probale(Prdno INT,CodeItem INT,prdQTY INT,Orderno int,Entrydate DATETIME,DelID int);  
Create Table #tbl_PackM (PID int,Orderno int)  
Create TABLE #tbl_PckDetail ( PDID int, PID int,Prdno int,CodeItem int,Qty int,Delid int)  
  
INSERT INTO #ItemMasterFile VALUES  
  (1,'A')  
, (2,'B')  
, (3,'C')  
, (4,'D')  
, (5,'e')  
, (6,'f')  
, (7,'g')  
, (8,'h')  
, (9,'K')  
, (10,'L')  
, (11,'M');  
  
INSERT INTO #SalesOrder VALUES  
  (001,'A_01','01-05-2019')  
  
  
INSERT INTO #OrderDetail VALUES  
  (1,001,1,5)  
, (2,001,2,3)  
, (3,001,3,2)  
  
  
INSERT INTO #Probale VALUES  
   (101,1,1,001,'01-06-2019',null)  
, (102,3,1,001,'02-06-2019',null)  
, (103,3,1,001,'02-06-2019',null)  
, (104,7,1,001,'02-06-2019',null)  
  
INSert into #tbl_PackM  values (1001,001)  
Insert into #tbl_PckDetail values (1111,1001,101,1,1,null),(1112,1001,102,2,1,null),(1113,1001,103,2,1,null)  

Note :
Descriptionitem= will come from #Itemmaster table
Orderqty = will come from #orderdetail table against orderno 001 which is in table #saleorder
Prdqty = will come from #Probale table qty sum.
Pending_qty = Orderqty-prdqty
Pack_qty = will come from #tbl_PckDetail table ,but against orderno ,which is in #tbl_PackM table

Answer 1

     ;with cte1 as (  
     Select  i.CodeItem as CodeItem,(I.Descriptionitem ) as Descriptionitem,  
     Isnull(SUM(P.prdqty),0)as Produce_QTY,P.OrderNo  
      From #ItemMasterFile I   
     left join #Probale P on I.CodeItem=P.CodeItem and P.OrderNO=001 and p.DelID is null  
     where   
     i.CodeItem IN(  
     SELECT CodeItem FROM #OrderDetail  
     UNION   
     SELECT CodeItem FROM #Probale  
     UNION   
     SELECT CodeItem FROM #tbl_PckDetail)  
     group by i.Descriptionitem,p.OrderNo,i.CodeItem)  
     ,cte2 as (SELECT I.CodeItem,I.Descriptionitem, D.orderno as OrderNo,  D.orderqty  
     FROM #OrderDetail D   
     inner  join #ItemMasterFile I on I.codeitem=D.Codeitem  
     WHERE (D.OrderNO = 001))  
     ,  
     cte3 as(  
     Select  i.CodeItem,(I.Descriptionitem )as Descriptionitem,  
     Isnull(SUM(PD.QTY),0)as Pack_QTy,M.Orderno  
     From #tbl_PckDetail PD  
     full  join #ItemMasterFile I on I.CodeItem=PD.CodeItem  
     inner join #tbl_PackM M on M.PID=PD.PID  
     where M.OrderNO=001 and PD.DelID is null  
     group by i.Descriptionitem,M.OrderNo,i.CodeItem  
     )  
      
     select   cte1.Descriptionitem,isnull(cte1.Produce_QTY,0)Produce_QTY,  
     ISNULL(cte2.orderqty,0)Orderqty,isnull(Pack_QTy,0)Pack_QTy,  
     isnull((cte1.Produce_QTY-cte3.Pack_QTy),0) Pending   
     from cte1  
     full  join cte2 on cte1.CodeItem=cte2.CodeItem  
     full  join cte3 on cte1.CodeItem=cte3.CodeItem  
    union   
    select 'Total' Total,isnull(sum(cte1.Produce_QTY),0)Produce_QTY,ISNULL(sum(cte2.orderqty),0)Orderqty,isnull(sum(Pack_QTy),0)Pack_QTy,'' as Pending from cte1  
      full  join cte2 on cte1.CodeItem=cte2.CodeItem  
       full    join cte3 on cte1.CodeItem=cte3.CodeItem  
     order by orderqty  
Output:  

Or:

  ;with cte1 as (  
 Select  i.CodeItem as CodeItem,(I.Descriptionitem )as Descriptionitem,  
 Isnull(SUM(P.prdqty),0)as Produce_QTY,P.OrderNo  
  From #Probale P  
  left join #ItemMasterFile I on I.CodeItem=P.CodeItem  
      
      
 where P.OrderNO=001 and p.DelID is null  
 group by i.Descriptionitem,p.OrderNo,i.CodeItem  
      
 )  
 ,cte2 as (SELECT I.CodeItem,I.Descriptionitem, D.orderno as OrderNo,  D.orderqty  
 FROM         #OrderDetail D inner  join #ItemMasterFile I on I.codeitem=D.Codeitem  
 WHERE     (D.OrderNO = 001)  
 ),  
      
 cte3 as(  
 Select  i.CodeItem,(I.Descriptionitem )as Descriptionitem,Isnull(SUM(PD.QTY),0)as Pack_QTy,M.Orderno  
  From #tbl_PckDetail PD  
 full  join #ItemMasterFile I on I.CodeItem=PD.CodeItem  
 inner join #tbl_PackM M on M.PID=PD.PID  
      
 where M.OrderNO=001 and PD.DelID is null  
 group by i.Descriptionitem,M.OrderNo,i.CodeItem  
 )  
 select COALESCE(cte1.Descriptionitem,cte2.Descriptionitem,cte3.Descriptionitem) Descriptionitem  
 ,isnull(cte1.Produce_QTY,0)Produce_QTY,ISNULL(cte2.orderqty,0)Orderqty,isnull(Pack_QTy,0)Pack_QTy,isnull((cte1.Produce_QTY-Pack_QTy),0) Pending from cte1  
  full  join cte2 on cte1.CodeItem=cte2.CodeItem  
  full     join cte3 on cte1.CodeItem=cte3.CodeItem  
      
      
 union   
      
 select 'Total' Total,isnull(sum(cte1.Produce_QTY),0)Produce_QTY,ISNULL(sum(cte2.orderqty),0)Orderqty,isnull(sum(Pack_QTy),0)Pack_QTy,'' as Pending from cte1  
  full  join cte2 on cte1.CodeItem=cte2.CodeItem  
   full    join cte3 on cte1.CodeItem=cte3.CodeItem  
 order by orderqty  
  

Output:

Answer 2

@EchoLiu-MSFT

i used your provided query,but it is not giving me correct output.
I tried to develop my query ,in which on issue is coming that if item quantity exit in #orderdetail table and does not exit in #probale and Packdetail table then that item quantity get display ,but their name is not getting display and coming null.

 CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50));  
 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 #Probale(Prdno INT,CodeItem INT,prdQTY INT,Orderno int,Entrydate DATETIME,DelID int);  
 Create Table #tbl_PackM (PID int,Orderno int)  
 Create TABLE #tbl_PckDetail ( PDID int, PID int,Prdno int,CodeItem int,Qty int,Delid int)  
      
 INSERT INTO #ItemMasterFile VALUES  
   (1,'A')  
 , (2,'B')  
 , (3,'C')  
 , (4,'D')  
 , (5,'e')  
 , (6,'f')  
 , (7,'g')  
 , (8,'h')  
 , (9,'K')  
 , (10,'L')  
 , (11,'M');  
      
 INSERT INTO #SalesOrder VALUES  
   (001,'A_01','01-05-2019')  
      
      
 INSERT INTO #OrderDetail VALUES  
   (1,001,1,5)  
 , (2,001,2,3)  
 , (3,001,3,2)  
      
      
 INSERT INTO #Probale VALUES  
    (101,1,1,001,'01-06-2019',null)  
 , (102,3,1,001,'02-06-2019',null)  
 , (103,3,1,001,'02-06-2019',null)  
 , (104,7,1,001,'02-06-2019',null)  
      
 INSert into #tbl_PackM  values (1001,001)  
 Insert into #tbl_PckDetail values (1111,1001,101,1,1,null),(1112,1001,102,2,1,null),(1113,1001,103,2,1,null)  
  
  
  
  
;with cte1 as (  
Select  i.CodeItem as CodeItem,(I.Descriptionitem )as Descriptionitem,  
Isnull(SUM(P.prdqty),0)as Produce_QTY,P.OrderNo  
 From #Probale P  
 left join #ItemMasterFile I on I.CodeItem=P.CodeItem  
  
  
where P.OrderNO=001 and p.DelID is null  
group by i.Descriptionitem,p.OrderNo,i.CodeItem  
  
)  
,cte2 as (SELECT I.CodeItem,I.Descriptionitem, D.orderno as OrderNo,  D.orderqty  
FROM         #OrderDetail D inner  join #ItemMasterFile I on I.codeitem=D.Codeitem  
WHERE     (D.OrderNO = 001)  
),  
  
cte3 as(  
Select  i.CodeItem,(I.Descriptionitem )as Descriptionitem,Isnull(SUM(PD.QTY),0)as Pack_QTy,M.Orderno  
 From #tbl_PckDetail PD  
full  join #ItemMasterFile I on I.CodeItem=PD.CodeItem  
inner join #tbl_PackM M on M.PID=PD.PID  
  
where M.OrderNO=001 and PD.DelID is null  
group by i.Descriptionitem,M.OrderNo,i.CodeItem  
)  
select cte1.Descriptionitem,isnull(cte1.Produce_QTY,0)Produce_QTY,ISNULL(cte2.orderqty,0)Orderqty,isnull(Pack_QTy,0)Pack_QTy,isnull((cte1.Produce_QTY-Pack_QTy),0) Pending from cte1  
 full  join cte2 on cte1.CodeItem=cte2.CodeItem  
 full     join cte3 on cte1.CodeItem=cte3.CodeItem  
  
  
union   
  
select 'Total' Total,isnull(sum(cte1.Produce_QTY),0)Produce_QTY,ISNULL(sum(cte2.orderqty),0)Orderqty,isnull(sum(Pack_QTy),0)Pack_QTy,'' as Pending from cte1  
 full  join cte2 on cte1.CodeItem=cte2.CodeItem  
  full    join cte3 on cte1.CodeItem=cte3.CodeItem  
order by orderqty  

Answer 3

Please check:

;WITH cte  
as(SELECT DISTINCT Descriptionitem,i.CodeItem,  
CASE WHEN o.orderqty IS NULL THEN 0 ELSE o.orderqty END orderqty  
,CASE WHEN SUM(p.prdQTY) OVER(PARTITION BY Descriptionitem) IS NULL   
THEN 0 ELSE SUM(p.prdQTY) OVER(PARTITION BY Descriptionitem) END prdQTY  
,CASE WHEN SUM(t.Qty) OVER(PARTITION BY Descriptionitem) IS NULL   
THEN 0 ELSE SUM(t.Qty) OVER(PARTITION BY Descriptionitem) END Pack_QTY  
FROM #ItemMasterFile i  
LEFT JOIN #OrderDetail o ON i.CodeItem=o.CodeItem  
LEFT JOIN #Probale p ON i.CodeItem=p.CodeItem  
LEFT JOIN #tbl_PckDetail t ON p.Prdno=t.Prdno  
WHERE i.CodeItem IN(  
SELECT CodeItem FROM #OrderDetail  
UNION   
SELECT CodeItem FROM #Probale  
UNION   
SELECT CodeItem FROM #tbl_PckDetail))  
  
SELECT Descriptionitem,orderqty,prdQTY,  
CASE WHEN orderqty-prdQTY>0 THEN orderqty-prdQTY  
ELSE 0 END Pending_qty,Pack_QTY  
FROM cte  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.