Expected Output Require

Analyst_SQL 3,551 Reputation points
2022-04-25T17:08:55.78+00:00

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)  

196292-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,276 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-29T06:44:19.227+00:00

    Hi,@Analyst_SQL
    197655-image.png
    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   
    

    197597-image.png

    Bert zhou

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-28T07:24:16.28+00:00

    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    
    

    197273-image.png
    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

    1 person found this answer helpful.