question

SAm2014 avatar image
SAm2014 asked

I want to modify the select statement below to give me the right result

![alt text][1] How do I modify the select statement below to give me the right result [1]: /storage/temp/1696-capture.jpg
selectsumisnullcase
capture.jpg (170.1 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please indicate all helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
JohnM avatar image
JohnM answered
You could try something along these lines: , CASE WHEN ISNULL(SUM(ContFee),0) = 0 THEN 0 ELSE AdminFee+SubFee+Confee end as 'TotalFee' EDIT: Adding in the SUM based on KenJ's comment. My fault. I didn't do any testing so I'm shooting from the hip. ;-) Hope that helps!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just throw the `SUM` in there and it should be golden
1 Like 1 ·
DOH!! Completely missed the SUM. I'll edit..
0 Likes 0 ·
sunithay avatar image
sunithay answered
DECLARE @FEETBL AS TABLE ( ID INT, CASEID INT, FEETYPE INT, COST INT ) INSERT INTO @FEETBL SELECT 1017796,2697789,29,50 UNION ALL SELECT 1018167,2697789,1,150 UNION ALL SELECT 1019493,2697789,15,30 UNION ALL SELECT 1017799,2697790,29,50 UNION ALL SELECT 1018168,2697790,1,150 UNION ALL SELECT 1019494,2697790,15,0 --SELECT * FROM @FEETBL SELECT *,(ADMINFEE+SUBFEE)'Total Admin',(CASE WHEN CONTFEE>0 THEN (ADMINFEE+SUBFEE+CONTFEE) ELSE 0 END) 'Total Fee' FROM ( SELECT T.CASEID,SUM(ADMINFEE) ADMINFEE,SUM(SUBFEE) SUBFEE,SUM(CONTFEE) CONTFEE FROM ( SELECT CASEID, (CASE WHEN F.FEETYPE=29 THEN F.COST ELSE 0 END) AS ADMINFEE, (CASE WHEN F.FEETYPE=1 THEN F.COST ELSE 0 END) AS SUBFEE, (CASE WHEN F.FEETYPE=15 THEN F.COST ELSE 0 END) AS CONTFEE FROM @FEETBL F )T GROUP BY CASEID )G
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.