Question on comparison
-
In my query below, is HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
the same as the value that my CASE statements come up with for [balance] ?
Isn't putting straight SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) overriding what I want to be there which is:
HAVING cb.CurrentBalance <> [balance] instead but it doesn't let me put [balance] there and I don't know why
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
-- Balance calculation based on what values are larger vs. smaller and addition or
-- subtraction based on + - of current numbers
CASE WHEN SUM(rf.rmstranamt) > rf10.rmstranamt10 Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
-- If both valus are zero, return zero
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < rf10.rmstranamt10 Then
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
-- Sum of All transaction amounts wtih code 10
INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
-- Sum of All transaction amounts that are not code 10
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt < 0 THEN
ABS(rmstranamt)
WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt >= 0 THEN
-ABS(rmstranamt)
WHEN rmstrancde IN ('55','56','57','58') THEN
ABS(rmstranamt)
WHEN rmstrancde NOT IN ('50','51','52','53','55','56','57','58') THEN
rmstranamt
ELSE
rmstranamt
END
 
As rmstranamtFROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
-- Current Balance which shows at the top of the account in RMS
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS ) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
-- Only bring back results where the transaction code is one of these
WHERE rf.rmstrancde IN ('10', '16','18','19','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '4264293999755337'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf10.rmstranamt10
-- Ensure that Curent Balance is not the same as the actual balance to give us a list of incorrect balances
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) <-------- SHOULD BE HAVING cb.CurrentBalance <> [balance]
AND cb.CurrentBalance <> 0.00
-
Whew - lotsa text..
I'd guess that - No, it's not the same as the case (though this is a guess)
But the case seems to to stuff, so I'd assume that it may evalute differently..
But what if.. if you just wrap the entire thing into parenthesis (making the entire thing a virtual table) excluding the HAVING clause, then use your preferred filter as a WHERE instead..?
select x.col1, x.col2....
from (
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
---- snip
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf10.rmstranamt10
) x
WHERE x.CurrentBalance <> x.balance
AND x.CurrentBalance <> 0.0
..would that work for you?
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply