For some reason, rmsacctnum is not being grouped and the 2 results I get should be added together at the end for , same for Sum rmstranamt and Sum rmstranamt 10 like in my 2nd query below. The first query is where the problem lies:
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
SUM(rf10.rmstranamt10) AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) <> 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND SUM(rf10.rmstranamt10) = 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
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
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','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 = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf.rmstranamt
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
OUTPUT:
-
4313030999894992 85.00 3265.12 3180.12 3528.43 <-- rmsacctnum is not grouped anymore!
4313030999894992 178.31 3265.12 3086.81 3528.43
Before I added the CASE Statements I had this and it was grouping and summing fine:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
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
WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
OUTPUT:
-
4313030999894992 263.31 6530.24 6266.93 3528.43
SOLVED IT
I had some missing SUM in my Case. Then took out rf.rmstranamt in my GROUP BY
