How to check for postive or negative then perform operation

[dba112233]

I need to check SUM(rf.rmstranamt) for positive or negative

Rules should be:
If SUM(rf.rmstranamt) is negative
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt) AS [Balance]
If SUM(rf.rmstranamt) is positive
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]

instead of how I am just subtractive everything below no matter if SUM(rf.rmstranamt) which is not right because I'll produce false positives


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

 

[SQLDenis]

Untested but it should work

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE SUM(rf.rmstranamt) when < 0 then SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
else SUM(rf10.rmstranamt10)- SUM(rf.rmstranamt) END AS [Balance]
cb.CurrentBalance
FROM RMASTER rm

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Oops 2 typo's

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE when SUM(rf.rmstranamt) < 0 then SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
else SUM(rf10.rmstranamt10)- SUM(rf.rmstranamt) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dba112233]

thanks, works great. Now on the contrary, I noticed that sometimes SUM(rf10.rmstranamt10) is negative. So when I have a situation where something like this:

SUM(rf10.rmstranamt10) SUM(rf.rmstranamt)
1 49

I need to make sure I don't get -48 so I need to add another clause to my CASE statement somehow to also check to see if SUM(rf10.rmstranamt10) is positive or negative

If postitive
Then instead take SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
If Negative
Then instead take SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

or maybe I can check the sizes of both, the biggger value then subtract that first...and do the + - check

 

[dba112233]

I think the best way to approach this is first I must check for which value is higher

Then do a series of Cases based on + -, - -, ++, - +

 

[dba112233]

because for example, the problem I have is

43950681 -60.00 40.38 -19.62 50.19

it should be 19.62 because we should really take 60.00-40.38 since in this case SUM(rf.rmstranamt) is bigger

 

[dba112233]

Here we go, I have this but my Case syntax isn't 100% formed well yet:

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (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 rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

 

[SimonSellick]

I'm not quite clear what you are trying to calculate here - can you explain? It looks as though you could probably save a fair bit of code by using abs() and sign():

for all n >= 0:

abs(-n) = abs = n
sign = 1
sign(-n) = -1 (not for n = 0)

You should be able to combine these and avoid the nested case statements.