Expected Output Need

Question

Below is data

 CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
 CREATE TABLE #Probale(BID INT,Orderno int,CodeItem INT,prdQTY INT,Entrydate DATETIME)    
 CREATE TABLE #SaleOrder(Orderno INT, Entrydate DATETIME)    
 Create Table #OrderDetail(ID int ,Orderno int,CodeItem INT,O_QTY int )     
 CREATE TABLE #tbl_BalPacM(PID INT, Orderno int,Entrydate DATETIME)    
 CREATE TABLE #tbl_PckDetail(DID INT,PID int,BID int,Codeitem int,Pack_Qty int)    
         
       
 INSERT INTO #ItemMasterFile VALUES(1,'A')    
 INSERT INTO #ItemMasterFile VALUES(2,'B')    
 INSERT INTO #ItemMasterFile VALUES(3,'C')    
 INSERT INTO #ItemMasterFile VALUES(4,'D')    
 INSERT INTO #ItemMasterFile VALUES(5,'e')    
 INSERT INTO #ItemMasterFile VALUES(6,'f')    
 INSERT INTO #ItemMasterFile VALUES(7,'g')    
 INSERT INTO #ItemMasterFile VALUES(8,'h')    
 INSERT INTO #ItemMasterFile VALUES(9,'K')    
 INSERT INTO #ItemMasterFile VALUES(10,'L')    
 INSERT INTO #ItemMasterFile VALUES(11,'M')    
       
	   
	          
 INSERT INTO #SaleOrder VALUES(111,'01-08-2022')    
 INSERT INTO #OrderDetail VALUES(1,111,1,1)    
 INSERT INTO #OrderDetail VALUES(2,111,3,12)    
 INSERT INTO #OrderDetail VALUES(3,111,11,13)    
 INSERT INTO #OrderDetail VALUES(4,111,10,14)    
 INSERT INTO #OrderDetail VALUES(5,111,8,14)     
  INSERT INTO #OrderDetail VALUES(6,111,7,12)    
         
 INSERT INTO #Probale VALUES(10011,111,1,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10012,111,3,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10013,111,11,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10014,111,10,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10015,111,8,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10016,111,9,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10017,112,9,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10018,112,8,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10019,111,1,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10020,111,3,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10021,111,6,1,'01-08-2022')    
 INSERT INTO #Probale VALUES(10022,111,10,1,'01-08-2022')      
      
 INSERT INTO #tbl_BalPacM VALUES(1001,111,'01-08-2022')    
      
         
 INSERT INTO #tbl_PckDetail VALUES(1,1001,10011,1,1)    
 INSERT INTO #tbl_PckDetail VALUES(2,1001,10012,3,1)    
 INSERT INTO #tbl_PckDetail VALUES(3,1001,10013,11,1)    
 INSERT INTO #tbl_PckDetail VALUES(4,1001,10014,10,1)    
 INSERT INTO #tbl_PckDetail VALUES(5,1001,10022,10,1)   
  INSERT INTO #tbl_PckDetail VALUES(5,1001,10021,6,1)   

Output
I want pass parameter of Orderno ,which is 111

O_QTY = From SalesOrder (Master) and OrderDetail (Detail) table,
Prd_Qty = From #Probale table.
Pack_Qty = From #tbl_BalPacM (Master) tbl_PckDetail (Detail) table,

Answer 1

Hi @Analyst_SQL
Check this query:

DECLARE @Orderno INT  
SET @Orderno = 111  
  
;WITH CTE1 AS  
(  
 SELECT S.Orderno,O.CodeItem,SUM(O_QTY) AS O_QTY  
 FROM #SaleOrder S JOIN #OrderDetail O ON S.Orderno=O.Orderno  
 WHERE S.Orderno = @Orderno   
 GROUP BY S.Orderno,O.CodeItem  
),CTE2 AS  
(  
 SELECT Orderno,CodeItem,SUM(prdQTY) AS prdQTY  
 FROM #Probale   
 WHERE Orderno = @Orderno  
 GROUP BY Orderno,CodeItem  
),CTE3 AS  
(  
 SELECT Orderno,Codeitem,SUM(Pack_Qty) AS Pack_Qty  
 FROM #tbl_BalPacM B JOIN #tbl_PckDetail P ON B.PID=P.PID  
 WHERE B.Orderno = @Orderno  
 GROUP BY Orderno,Codeitem  
)  
SELECT UPPER(I.Descriptionitem)AS Descriptionitem  
      ,ISNULL(C1.O_QTY,0)AS O_QTY  
      ,ISNULL(C2.prdQTY,0)AS Prd_QTY  
	  ,ISNULL(C3.Pack_Qty,0)AS Pack_Qty  
FROM #ItemMasterFile I LEFT JOIN CTE1 C1 ON I.CodeItem=C1.CodeItem  
					   LEFT JOIN CTE2 C2 ON I.CodeItem=C2.CodeItem  
					   LEFT JOIN CTE3 C3 ON I.CodeItem=C3.CodeItem  
WHERE C1.O_QTY IS NOT NULL OR C2.prdQTY IS NOT NULL OR C3.Pack_Qty IS NOT NULL  

Output:

Best regards,
LiHong