question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked akhterhussain-3167 commented

Group by imp_Container_No

Below is data, when ever two time Imp_Container_No exit with same name,then it imp_Inv_Value and Inv_Weight will get sum below is data. Total=imp_Inv_Value*Inv_Currency_Pkr

 Create table #tbl_Account_L_Five (Level_Five_Id int,Level_Five_Name varchar(50))
 Create table #tbl_Imp_Master_Heco (Imp_ID int,
       
            Imp_Ship_Code varchar(50),
           Imp_Currency_Rate decimal(10,2),
           Imp_Container_No varchar(50),
           Level_Five_Id int,
           Imp_Date date,
         )
    
         Create table #tbl_Imp_Invoice_Heco
         (ID int,Imp_Inv_ID varchar(50),imp_ID int,imp_Inv_Value Decimal(10,2),Inv_Weight Decimal(10,2),Inv_Currency_Pkr Decimal(10,2)
         ,Imp_Supplier_Asset_ID int,Imp_Currency_Rate Decimal(10,2),
            
         Imp_Supplier_Libility int,Imp_Code varchar(50))
    
    
         insert into #tbl_Account_L_Five values (10001,'Akhter')
         insert into #tbl_Account_L_Five values (10002,'Jhoni')
         insert into #tbl_Account_L_Five values (10003,'Kami')
                 insert into #tbl_Account_L_Five values (10004,'Sami')
    
         insert into #tbl_Imp_Master_Heco values(111,'Has-001',150,'Abc123',10004,'2021-06-01')
         insert into #tbl_Imp_Master_Heco values(112,'Has-002',150,'Abc122',10004,'2021-06-02')
    
             Insert into #tbl_Imp_Invoice_Heco values(1,'INV_01',111,105,502.2,150,10002,142,null,'Imp_01')
             Insert into #tbl_Imp_Invoice_Heco values(2,'INV_02',112,751,222.2,150,10003,140,null,'Imp_02')
             Insert into #tbl_Imp_Invoice_Heco values(3,'INV_12',112,222,512,250,10002,140,null,'Imp_03')

104846-image.png


sql-server-generalsql-server-transact-sql
image.png (16.5 KiB)
· 1
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,

Could you please validate the latest answer and provide any update?

If it is not working, please provide more sample data and expected output.

Thanks for your understanding.

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @akhterhussain-3167,

I compared the sample data and actual data you provided, one difference is Imp_Supplier_Libility column that one is null and one is not null.

Please have a try with below updated one:

 ;with cte as (
 select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
 ,a.Imp_Date,min(b.Imp_Inv_ID) Imp_Inv_ID,sum(b.imp_Inv_Value)imp_Inv_Value
 ,sum(b.Inv_Currency_Pkr) Inv_Currency_Pkr,max(b.Imp_Supplier_Asset_ID) Imp_Supplier_Asset_ID
 ,b.Imp_Currency_Rate,min(b.Imp_Supplier_Libility_ID) Imp_Supplier_Libility_ID,min(b.Imp_Code) Imp_Code
 from tbl_Imp_Master_Heco a 
 left join tbl_Imp_Invoice_Heco b on a.Imp_ID=b.imp_ID
 left join tbl_Account_L_Five c on a.Imp_Shipping_Line=c.Level_Five_Id
 group by a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
 ,a.Imp_Date,b.Imp_Currency_Rate)
 select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,a.Level_Five_Name,a.Imp_Date
 ,a.Imp_Inv_ID,a.imp_Inv_Value,a.Inv_Currency_Pkr 
 ,b.Level_Five_Name Imp_Supplier_Asset_ID 
 ,a.Imp_Currency_Rate,a.Imp_Supplier_Libility_ID,a.Imp_Code
 ,imp_Inv_Value*Imp_Currency_Rate Total from cte a 
 left join tbl_Account_L_Five b on a.Imp_Supplier_Asset_ID=b.Level_Five_Id

If above is still not working with your actual data, please provide some of your actual data or more sample data for further updating.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

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.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @akhterhussain-3167,

According to your sample data, your total could be imp_Inv_Value*Imp_Currency_Rate instead of imp_Inv_Value*Inv_Currency_Pkr mentioned by you.

Please refer below:

 ;with cte as (
 select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
 ,a.Imp_Date,min(b.Imp_Inv_ID) Imp_Inv_ID,sum(b.imp_Inv_Value)imp_Inv_Value
 ,sum(b.Inv_Currency_Pkr) Inv_Currency_Pkr,max(b.Imp_Supplier_Asset_ID) Imp_Supplier_Asset_ID
 ,b.Imp_Currency_Rate,b.Imp_Supplier_Libility,min(b.Imp_Code) Imp_Code
 from #tbl_Imp_Master_Heco a 
 left join #tbl_Imp_Invoice_Heco b on a.Imp_ID=b.imp_ID
 left join #tbl_Account_L_Five c on a.Level_Five_Id=c.Level_Five_Id
 group by a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
 ,a.Imp_Date,b.Imp_Currency_Rate,b.Imp_Supplier_Libility)
 select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,a.Level_Five_Name,a.Imp_Date
 ,a.Imp_Inv_ID,a.imp_Inv_Value,a.Inv_Currency_Pkr 
 ,b.Level_Five_Name Imp_Supplier_Asset_ID 
 ,a.Imp_Currency_Rate,a.Imp_Supplier_Libility,a.Imp_Code
 ,imp_Inv_Value*Imp_Currency_Rate Total from cte a 
 left join #tbl_Account_L_Five b on a.Imp_Supplier_Asset_ID=b.Level_Five_Id

Output:
105211-output.png

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (8.5 KiB)
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.

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 answered akhterhussain-3167 commented

@MelissaMa-msft

I modify little bit query, but issue still same

                  ;with cte as (
      select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
      ,a.Imp_Date,min(b.Imp_Inv_ID) Imp_Inv_ID,sum(b.imp_Inv_Value)imp_Inv_Value
      ,sum(b.Inv_Currency_Pkr) Inv_Currency_Pkr,max(b.Imp_Supplier_Asset_ID) Imp_Supplier_Asset_ID
      ,b.Imp_Currency_Rate,b.Imp_Supplier_Libility_ID,min(b.Imp_Code) Imp_Code
      from tbl_Imp_Master_Heco a 
      left join tbl_Imp_Invoice_Heco b on a.Imp_ID=b.imp_ID
      left join tbl_Account_L_Five c on a.Imp_Shipping_Line=c.Level_Five_Id
      group by a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,c.Level_Five_Name
      ,a.Imp_Date,b.Imp_Currency_Rate,b.Imp_Supplier_Libility_ID)
      select a.Imp_ID,a.Imp_Ship_Code,a.Imp_Container_No,a.Level_Five_Name,a.Imp_Date
      ,a.Imp_Inv_ID,a.imp_Inv_Value,a.Inv_Currency_Pkr 
      ,b.Level_Five_Name Imp_Supplier_Asset_ID 
      ,a.Imp_Currency_Rate,a.Imp_Supplier_Libility_ID,a.Imp_Code
      ,imp_Inv_Value*Imp_Currency_Rate Total from cte a 
      left join tbl_Account_L_Five b on a.Imp_Supplier_Asset_ID=b.Level_Five_Id
        
     OutPut is coming Imp_ID is coming two times.

105714-image.png



image.png (31.6 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.

The issue is not the query but the recruitment and database architecture!

Until you solve the point I mentioned no one will be able to provide you a good solution and what people gave you was a query which fit specific data sample and not a solution to your needs (according to the description we have here).

Please go back to my responses above and provide the missing information!

Always remember that a solution should cover future data and not only current data in the table. For a good solution we first need a good description which cover all options and well-defined requirements :-)

Note! It is very difficult (almost impossible) to follow our thread in this forum. I am trying to check threads that I participated in but there is no option to get a list of threads so if you provide the information and continue the discussion and I do not respond in a day or two then you can try ping me. It is technical limitation of the forum.

1 Vote 1 ·

Hi @pituach ,

My requirement is this, when Ever Imp_ID is same in tbl_Imp_Invoice_Heco,

               Insert into tbl_Imp_Invoice_Heco values(1,'INV_01',111,105,502.2,150,10002,142,null,'Imp_01')
               Insert into tbl_Imp_Invoice_Heco values(2,'INV_02',112,751,222.2,150,10003,140,null,'Imp_02')
               Insert into tbl_Imp_Invoice_Heco values(3,'INV_12',112,222,512,250,10002,140,null,'Imp_03')


then that entry will be sum and display one row,

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Hi,

the description is missing something

when ever two rows have the same value for Imp_Container_No then the column Imp_Currency_Rate is not the same in your sample data, so which one do you present in the result?!?

Check out the result of this simple JOIN

 select 
     t1.imp_ID, Imp_Ship_Code, Imp_Container_No, Level_Five_Id, Imp_Date, 
     imp_Inv_Value, Inv_Weight, Imp_Supplier_Asset_ID, t1.Imp_Currency_Rate,
     Imp_Supplier_Libility, Imp_Code
 from tbl_Imp_Invoice_Heco t1
 Left Join tbl_Imp_Master_Heco t2 ON t1.imp_ID = t2.Imp_ID


It is not clear why when you group the data you get the value Imp_02 and not Imp_03 in the result of the column Imp_Code

Please try to clarify the result which you present


------------- demo ------------

 Create table tbl_Account_L_Five (Level_Five_Id int,Level_Five_Name varchar(50))
 Create table tbl_Imp_Master_Heco (Imp_ID int,
           
             Imp_Ship_Code varchar(50),
            Imp_Currency_Rate decimal(10,2),
            Imp_Container_No varchar(50),
            Level_Five_Id int,
            Imp_Date date,
          )
        
          Create table tbl_Imp_Invoice_Heco
          (ID int,Imp_Inv_ID varchar(50),imp_ID int,imp_Inv_Value Decimal(10,2),Inv_Weight Decimal(10,2),Inv_Currency_Pkr Decimal(10,2)
          ,Imp_Supplier_Asset_ID int,Imp_Currency_Rate Decimal(10,2),
                
          Imp_Supplier_Libility int,Imp_Code varchar(50))
        
        
          insert into tbl_Account_L_Five values (10001,'Akhter')
          insert into tbl_Account_L_Five values (10002,'Jhoni')
          insert into tbl_Account_L_Five values (10003,'Kami')
          insert into tbl_Account_L_Five values (10004,'Sami')
        
          insert into tbl_Imp_Master_Heco values(111,'Has-001',150,'Abc123',10004,'2021-06-01')
          insert into tbl_Imp_Master_Heco values(112,'Has-002',150,'Abc122',10004,'2021-06-02')
        
              Insert into tbl_Imp_Invoice_Heco values(1,'INV_01',111,105,502.2,150,10002,142,null,'Imp_01')
              Insert into tbl_Imp_Invoice_Heco values(2,'INV_02',112,751,222.2,150,10003,140,null,'Imp_02')
              Insert into tbl_Imp_Invoice_Heco values(3,'INV_12',112,222,512,250,10002,140,null,'Imp_03')
    
    
 select * from tbl_Account_L_Five
 select * from tbl_Imp_Master_Heco
 select * from tbl_Imp_Invoice_Heco
    
    
 select 
     t1.imp_ID, Imp_Ship_Code, Imp_Container_No, Level_Five_Id, Imp_Date, 
     imp_Inv_Value, Inv_Weight, Imp_Supplier_Asset_ID, t1.Imp_Currency_Rate,
     Imp_Supplier_Libility, Imp_Code
 from tbl_Imp_Invoice_Heco t1
 Left Join tbl_Imp_Master_Heco t2 ON t1.imp_ID = t2.Imp_ID

Result of the join:
104925-image.png



image.png (22.4 KiB)
· 4
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.

sorry,
i want that ,could it possible that if Imp_ID is coming two time then ,query make it sum,as i share image about

0 Votes 0 ·
pituach avatar image pituach akhterhussain-3167 ·

Please re-read my message and try to understand the issue in your description

It is very simple to get the sum of the column imp_Inv_Value but (1) it is not clear by which columns you want to group by - we can group by all other columns which make no sense. (2) Why in your expected result you get the value Imp_02 and not Imp_03 in the column Imp_Code ?!?

You need to explain the logic of how you get your expected result from the sample data

0 Votes 0 ·

i got you,i have done already which your provided thanks,but my requirement is little bit different,

    Insert into #tbl_Imp_Invoice_Heco values(2,'INV_02',112,751,222.2,150,10003,140,null,'Imp_02')
              Insert into #tbl_Imp_Invoice_Heco values(3,'INV_12',112,222,512,250,10002,140,null,'Imp_03')

I am saying ,where Imp_ID is same ,as you see 112 is coming two times ,then sum Imp_Inv_Value and sum Total ,display one Inv_ID,
Imp_Code=Imp_02 and Imp_03 ,display one of them Imp_02

0 Votes 0 ·
Show more comments