Help with HAVING clause where SELECT columns are SUM(CASE)

[mlibeson]

I am trying to return only those grouped rows where the OriginalAmt does not equal the ReverseAmt. Instead of the query giving me the rows I want, it returns all rows.

Please help. Need ASAP.

SELECT h.Account, h.ApplSeq,
SUM(CASE WHEN h.Dcindicator = 0 THEN h.Tramt ELSE 0.0 END) AS 'OriginalAmt',
SUM(CASE WHEN h.Dcindicator = 1 THEN h.Tramt ELSE 0.0 END) AS 'ReverseAmt',
(SELECT SUM(Tramt) FROM THistory x WITH (NOLOCK) WHERE x.Account = h.Account AND x.ApplSeq = h.ApplSeq AND dbo.fn_GetTranxCodeCPA(x.Trntype) = 'P' AND x.Trncode LIKE 'TBI[PST]') AS 'TBI_AMT'
FROM THistory h WITH (NOLOCK)
WHERE h.Trncode = 'RLNS'
AND h.dtePosted_Date >= CAST('01/01/2009' AS DATETIME)
AND EXISTS(SELECT * FROM THisIns i WHERE i.Account = h.Account AND i.ApplSeq = h.ApplSeq AND i.Inscode = 'BCFP')
GROUP BY h.Account, h.ApplSeq
HAVING SUM(CASE WHEN h.Dcindicator = 0 THEN h.Tramt ELSE 0.0 END) <> SUM(CASE WHEN h.Dcindicator = 1 THEN h.Tramt ELSE 0.0 END)
ORDER BY 1, 2;

I also tried:

HAVING 'OriginalAmt' <> 'ReverseAmt'

Michael Libeson

 

[markros]

By quick glance your first way looks OK to me.

If you can not make it to work, you can always use it as derived table, e.g.

select * from (select myCurrentSelectWithoutHaving) X
where OriginalAmt <> ReverseAmt

 

[mlibeson]

True, I can used a derived table. Thank you for you post.

I would still like to understand why it did not return what I expected. In this case the data set was small enough and easy enough to recognize that there was an issue. I am afriad that I may use something similar and not catch that there is a problem as the query ran without errors.

Michael Libeson

 

[markros]

What is your SQL Server version?

I see few potential optimizations for this query (say, why do you use subquery instead of derived table - derived tables perform much faster than subqueries in 99% of cases), but I don't see why it would not perform correctly.

I would suggest you to also post at

http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

forum and preferrably provide table creation scripts with some data to show the problem. Just by looking at this query I believe it suppose to work with having clause the same way as with derived table, so, you could have found some weird bug.

 

[mlibeson]

OK, I am sorry for this post. I was so focused on the end result and the numbers being compared where the two columns involved are opposites of each other where if you add them together, the value is 0. I was so focused at seeing the positive and negative numbers as the same that I ignored the fact that one column had a negative and SQL is not going to read my mind to understand the fact that although one number is negative that they are actually the same number. I changed the code to add the 2 fields and report if the sum was not 0. Problem sovled. Again I am sorry for the post. I hope you have a good laugh at this one. I know I did. Sorry to have wasted your time.

Michael Libeson

 

[markros]

Well, now I can laugh a bit once I understand it. I guess you could have solved it by adding ABS to each sum and still comparing them.