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

Answer 1

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.