question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked BertZhoumsft-7490 edited

Expected Output Require

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-generalsql-server-transact-sql
image.png (11.0 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167
One thing I don't understand is how Level_Four_Name 'CCC' appears in the final output.
Please check the image below:
197657-image.png
Also please correct me if I misunderstand it.

Best regards,
LiHong

1 Vote 1 ·
image.png (15.9 KiB)

@LiHongMSFT-3908 yes ,i updated again.

197772-196292-image.jpg


0 Votes 0 ·
196292-image.jpg (37.1 KiB)

Hi,@akhterhussain-3167

Sorry to remind you again, I guess you didn't notice the picture of lihong above . Since you are connecting three tables, find the CID of SUPPLIER_id in the same table through leve_FOUR_ID, and use this value to calculate the qty and weight of the table below.

That is to say , there are two records on the picture with a cid of 222, so your qty will be 2 . At this time , the weight is equal to the sum of the two records and should be 200 . For example, your picture is 100, which cannot be calculated , and is completely inconsistent with the result of multi-table connection.
198115-image.png
For example, the supplier_ID of AAA is 221, which corresponds to the first three records in the bottom graph, so the qty is 3 and the weight is 300. Please correct.

Bert zhou



0 Votes 0 ·
image.png (27.3 KiB)

Hi,@akhterhussain-3167
Please check the links of #ItemMasterFile and #Probale. After I got the results of QTY and Weight in the local test, the link was found to be null.
The specific problem appears in the links of the two tables behind DescriptionItem. I look forward to your follow-up answer.

Best regards,
Bert Zhou

0 Votes 0 ·


i am not understanding
the link was found to be null.
The specific problem appears in the links of the two tables behind DescriptionItem.

I have two table #Probale ,in which my production data is inserting and second one #tbl_ContD as well.

0 Votes 0 ·

i tried to generate below query ,but not giving me desire output

        ;with cte_D as(
    
               select  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
                                                                                   left 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 i.Codeitem,i.Descriptionitem,c.Cat_name    
                                                                                        
                                                                                     )
    
                                                                                     ,Cte_P as (
  select  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
                                                                                     inner join #Concategory c on c.Cat_ID=P.cat_ID
                                                                                          group by i.Codeitem,i.Descriptionitem,c.Cat_name
                                                                                     )
             select  Isnull(D.Descriptionitem,P.Descriptionitem)Descriptionitem,(isnull(P.Weight,D.Weight))Weight,isnull(p.Cat_name,D.Cat_name)Nature  
              from cte_D D full outer Join Cte_P P on D.Codeitem=P.Codeitem
0 Votes 0 ·
BertZhoumsft-7490 avatar image
1 Vote"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@akhterhussain-3167
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



image.png (19.3 KiB)
image.png (17.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BertZhoumsft-7490

  How to marks your last given solution as Answered
1 Vote 1 ·

Hi,@akhterhussain-3167

Thanks for your reply, I have switched from comment to answer, You can adopt that answer in the comments on the second floor.

Bert Zhou

0 Votes 0 ·
BertZhoumsft-7490 avatar image
1 Vote"
BertZhoumsft-7490 answered BertZhoumsft-7490 converted comment to answer

Hi,@akhterhussain-3167

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




image.png (30.5 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BertZhoumsft-7490

 Please verify your metadata for errors, Maybe you mistakenly input 444 into 333.    

No, Metadata is correct, some time Level_Four_Name(Level_Four_ID) will be null.

0 Votes 0 ·

Hi,@akhterhussain-3167
Let me emphasize to you again that if there is no problem with your original data, then what you have achieved should be the result shown in the picture below. As a result, I have tested locally. thanks.



1 Vote 1 ·

i updated again my output

197714-196292-image.jpg


0 Votes 0 ·
196292-image.jpg (37.1 KiB)