Expected Output Require

Question

Below is Data and Column condition define

Level_Four_Name = will take from #tbl_Account_L_Four table,it is link with #Containerno table, then #Containerno table link to #tbl_ContD table

Descriptionitem= will take from #ItemMasterFile ,it is link with #Probale and #tbl_ContD table.

QTY = will take from #Probale and #tbl_ContD table.

weight = will take from #Probale and #tbl_ContD table.

Cat_name = will take from #Concategory and link with #Probale and #tbl_ContD table.

Note :QTY and weigth will be group by Supplier ,if supplier will not exists then group by Cat_Name

 Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),TID int)  
 insert into   #ItemMasterFile values (1,'W Colour TShirt',2)  
  insert into   #ItemMasterFile values (2,'W Colour Pant',2)  
   
 Create table #Concategory (Cat_ID int,Cat_name varchar(50))  
  
 insert into   #Concategory values (1,'t1')  
  insert into   #Concategory values (2,'t5')  
  
 Create table #Probale (Prdno int,Codeitem int ,Weigth int,prdqty int,cat_ID int)  
  
      insert into   #Probale values (10001,1,475,1,2)  
      insert into   #Probale values (10002,1,438,1,2)  
  
     Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50))  
    insert into   #tbl_Account_L_Four values (101,'AAA')  
     insert into   #tbl_Account_L_Four values (102,'BBB')  
 insert into   #tbl_Account_L_Four values (103,'CCC')  
  
    Create table #Containerno (CID int,contno varchar(50),Supplier_ID int)  
  
       insert into   #Containerno values (221,'wiper1',101)  
   insert into   #Containerno values (222,'wiper1',102)  
     insert into   #Containerno values (333,'wiper1',103)  
   insert into   #Containerno values (444,'wiper1','')  
    Create table #tbl_ContD (D_ID int,cid int,Codeitem int ,C_Weight int,qty int,cat_ID int)  
  
      insert into   #tbl_ContD values (122,221,1,100,1,null)  
          insert into   #tbl_ContD values (133,221,1,100,1,null)  
          insert into   #tbl_ContD values (144,221,1,100,1,null)  
          insert into   #tbl_ContD values (155,222,1,100,1,null)  
          insert into   #tbl_ContD values (166,444,1,100,1,1)  
          insert into   #tbl_ContD values (177,444,1,100,1,1)  
          insert into  #tbl_ContD values (188,222,1,100,1,null)  

Answer 1

Hi,@Analyst_SQL

You misunderstood what I meant , I asked you to check the value of #tbl_CONTD after the connection, according to your thinking, QTY must be 2.Try this:

;with cte_D as(  
select  F.Level_Four_Name,I.Codeitem,I.Descriptionitem,sum(D.C_Weight)Weight,sum(D.qty)Qty ,C.Cat_name  
from #ItemMasterFile I inner join #tbl_ContD D on D.Codeitem=I.Codeitem   
					   left join #Containerno con on con.CID=d.cid  
                       full outer join #tbl_Account_L_Four F on F.Level_Four_ID=con.Supplier_ID  
                       left join #Concategory c on c.Cat_ID=d.cat_ID  
group by F.Level_Four_Name,i.Codeitem,i.Descriptionitem,c.Cat_name   
union all  
select  ' 'as Level_Four_Name,I.Codeitem,I.Descriptionitem,sum(p.Weigth)Weight,sum(P.prdqty)Qty ,C.Cat_name    
from #ItemMasterFile I inner join #Probale P on P.Codeitem=I.Codeitem  
                       left join #Concategory c on c.Cat_ID=P.cat_ID  
group by i.Codeitem,i.Descriptionitem,c.Cat_name  
)  
select Level_Four_Name, isnull(null,Descriptionitem)Descriptionitem,  
isnull(null,Qty)QTY,  
isnull(null,Weight)Weight,isnull(null,Cat_name)cat_name  
from cte_D   

Bert zhou

Answer 2

Hi，@Analyst_SQL

Welcome to Microsoft T-SQL Q&A Forum!

There is a problem with your expected result, please see the feedback of the execution process below :

 select  F.Level_Four_Name,I.Codeitem,I.Descriptionitem,sum(D.C_Weight)Weight,sum(D.qty)Qty ,C.Cat_name  
 from #ItemMasterFile I inner join #tbl_ContD D on D.Codeitem=I.Codeitem   
 inner join #Containerno con on con.CID=d.cid  
full join #tbl_Account_L_Four F on F.Level_Four_ID=con.Supplier_ID  
left join #Concategory c on c.Cat_ID=d.cat_ID  
group by  F.Level_Four_Name,i.Codeitem,i.Descriptionitem,c.Cat_name    

Pay attention to here Level_Four_Name:CC, there is a null value, according to your description, the #tbl_Account_L_Four table is linked with the #Containerno table to obtain CCC, but when linking with contd, the CID number of 333 is not retrieved, Please verify your metadata for errors, Maybe you mistakenly input 444 into 333.

Best regards,
Bert Zhou