Question on value

[dba112233]

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 rmstranamt

FROM 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

 

[AtomicWedgie]

Hi,

what if you put the HAVING cb.CurrentBalance <> [balance]
in the WHERE/AND

AND cb.CurrentBalance <> [balance]
AND cb.CurrentBalance <> 0.00

and then reomove the having completely



Regards,

Atomic Wedgie