question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked kasimmohamed-6993 commented

Sum based on different condition

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

sql-server-generalsql-server-transact-sql
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.

1 Answer

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered kasimmohamed-6993 commented

Hi @kasimmohamed-6993
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:
205441-image.png
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.


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

Thanks alot LiHongMSFT

0 Votes 0 ·