question

KRA188 avatar image
KRA188 asked

Sum using Max Case not showing all information

The issue I am having is that when the query is ran, I am not getting all the returns I want. I am not sure if I am using the correct function in getting the returns and was wondering if anyone had a better idea on how I could get this information. Running the query the results are as below: ![alt text][1] A = Gross Code 0 B = Half Time Code 202 C = Gross Less HT ( A – B = C) if no B the A = C D = Time One Half Code 200 E = Multiplier for D F = Adj Thrd OT ( D * .3333 = F) G = Total (A – B – F = G) if there is no B or F then A=G SELECT dbo.prempacm.prcc_emp, dbo.prempmst.prem_lname, dbo.prempmst.prem_fname, dbo.prempmst.prem_minit, dbo.prpmppay.prpp_risk, MAX(case WHEN dbo.prempacm.prcc_cd = '0' then dbo.prempacm.prcc_gross_amt end) AS A, MAX(case WHEN dbo.prempacm.prcc_cd = '202' then dbo.prempacm.prcc_gross_amt end) AS B, MAX(CASE WHEN dbo.prempacm.prcc_cd = '0' THEN dbo.prempacm.prcc_gross_amt end) - MAX(CASE WHEN dbo.prempacm.prcc_cd = '202' THEN dbo.prempacm.prcc_gross_amt END) AS C, MAX(case WHEN dbo.prempacm.prcc_cd = '200' then dbo.prempacm.prcc_gross_amt end) AS D, MAX(CASE WHEN dbo.prempacm.prcc_cd = '200' THEN dbo.prempacm.prcc_gross_amt *.3333 END) AS F, MAX(CASE WHEN dbo.prempacm.prcc_cd = '0' THEN dbo.prempacm.prcc_gross_amt end) - MAX(CASE WHEN dbo.prempacm.prcc_cd = '200' THEN dbo.prempacm.prcc_gross_amt *.3333 END) - MAX(CASE WHEN dbo.prempacm.prcc_cd = '202' THEN dbo.prempacm.prcc_gross_amt END) AS G FROM dbo.prempacm(NOLOCK), dbo.prpmppay(NOLOCK), dbo.prempmst(NOLOCK) WHERE dbo.prempacm.prcc_emp = dbo.prpmppay.prpp_emp and dbo.prempacm.prcc_emp = dbo.prempmst.prem_emp and dbo.prpmppay.prpp_risk <> '' GROUP BY dbo.prempacm.prcc_emp, dbo.prempmst.prem_lname, dbo.prempmst.prem_fname, dbo.prempmst.prem_minit, dbo.prpmppay.prpp_risk Order By dbo.prpmppay.prpp_risk, dbo.prempacm.prcc_emp ASC [1]: /storage/temp/1185-sql+q.gif
case
sql q.gif (23.1 KiB)
10 |1200

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

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
You are not receiving what you want, because in case B or F is NULL then you will receive for C and G also NULL. To receive exactly what you request, you should use ISNULL or COALESCE when Subtracting the expressions of B or F. So for example your expression for column C should look like: MAX(CASE WHEN dbo.prempacm.prcc_cd = '0' THEN dbo.prempacm.prcc_gross_amt end) - ISNULL(MAX(CASE WHEN dbo.prempacm.prcc_cd = '202' THEN dbo.prempacm.prcc_gross_amt END), 0) AS C The same for column G: MAX(CASE WHEN dbo.prempacm.prcc_cd = '0' THEN dbo.prempacm.prcc_gross_amt end) - ISNULL(MAX(CASE WHEN dbo.prempacm.prcc_cd = '200' THEN dbo.prempacm.prcc_gross_amt *.3333 END), 0) - ISNULL(MAX(CASE WHEN dbo.prempacm.prcc_cd = '202' THEN dbo.prempacm.prcc_gross_amt END) , 0) AS G
2 comments
10 |1200

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

Brilliant! Works like a champ. Thank you so much. I knew it had to do with the NULL values but I couldn't wrap my head around it.,Brilliant! Thank you very much I knew it had to do with the Null values but couldn't wrap my head around it.
0 Likes 0 ·
You might want to upvote the answer as well as marking it as correct!
0 Likes 0 ·

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.