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
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.
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!
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
17 People are following this question.
