Sum based on different condition

Question

Hi,

I have table like below

create table ##Main (ID nvarchar(50), TDate date, TType int)
create table ##Sub (ID nvarchar(50), SType nvarchar(2), SCode nvarchar(10), Net Decimal(18,2))

insert into ##Main values ('1001','2022-05-01', 1)
insert into ##Main values ('1002','2022-05-01', 2)

insert into ##Main values ('1004','2022-05-02', 1)
insert into ##Main values ('1005','2022-05-02', 1)
insert into ##Main values ('1006','2022-05-02', 3)

insert into ##Main values ('1007','2022-05-03', 1)
insert into ##Main values ('1008','2022-05-03', 2)
insert into ##Main values ('1009','2022-05-03', 3)

insert into ##Sub values ('1001', '11', 'ABC', 100)
insert into ##Sub values ('1001', '33', 'ABC', 200)
insert into ##Sub values ('1002', '22', 'ABC', 150)
insert into ##Sub values ('1002', '44', 'ABC', 300)

insert into ##Sub values ('1004', '11', 'ABC', 100)
insert into ##Sub values ('1004', '22', 'ABC', 50)
insert into ##Sub values ('1005', '22', 'ABC', 250)
insert into ##Sub values ('1006', '22', 'ABC', 120)
insert into ##Sub values ('1006', '11', 'ABC', 200)

insert into ##Sub values ('1007', '11', 'ABC', 100)
insert into ##Sub values ('1008', '22', 'ABC', 50)
insert into ##Sub values ('1008', '22', 'XYZ', 150)
insert into ##Sub values ('1009', '22', 'XYZ', 100)
insert into ##Sub values ('1009', '11', 'XYZ', 50)

select * from ##Main
select * from ##Sub
drop table ##Main
drop table ##Sub

I need the result table like below

create table ##Result (TDate Date, A Decimal(18,2) , B Decimal(18,2) , C Decimal(18,2))

insert into ##Result values ('2022-05-01',600,150,0)
insert into ##Result values ('2022-05-02',600,120,0)
insert into ##Result values ('2022-05-03',100,300,50)
select * from ##Result
drop table ##Result

my condition is

Need to Calculate A,B,C amount against date
Condition for A:

if Main table 'TType' value is 1 then sum Net in Sub Table
if Main table 'TType' value is 2 then sum Net other than SType=22 or SCode=XYZ
if Main table 'TType' value is 3 then sum Net other than SType=22

Condition for B:

if Main table 'TType' value is 2 or 3 then sum Net in Sub Table where SType=22

Condition for C:

if Main table 'TType' value is 3 then sum Net in Sub Table where SType<>22 and SCode=XYZ

Thanks

Accepted Answer

Hi @kasim mohamed
Check this:

SELECT TDate  
      ,SUM(CASE WHEN TType=1 THEN Net   
                WHEN TType=2 AND SType<>'22' AND SCode<>'XYZ' THEN Net   
                WHEN TType=3 AND SType<>'22' THEN Net ELSE 0 END) AS A  
      ,SUM(CASE WHEN TType IN(2,3) AND SType = '22' THEN Net ELSE 0 END) AS B  
      ,SUM(CASE WHEN TType =3 AND SType <> '22' AND SCode ='XYZ' THEN Net ELSE 0 END) AS C  
FROM ##Main M JOIN ##Sub S ON M.ID=S.ID  
GROUP BY TDate

Output:

And I found that the column_A value on 2022-5-03 in the output is not the same as your result table, please check it. If i understand your logic right, it should be 150, not 100.

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Sum based on different condition

0 additional answers