SUM not working

[dba112233]

Here's the SUM portion of my code:

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstrancde < 0 THEN
rmstranamt
WHEN rmstrancde IN ('50','51','52','53') AND rmstrancde >= 0 THEN
-ABS(rmstranamt)
WHEN rmstrancde IN ('55','56','57','58') THEN
ABS(rmstranamt)
ELSE
rmstranamt
END
) As rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

The values incoming are:

rmstranamt rmstrancde
50.00 51
50.00 51
50.00 51
50.00 51
50.00 51
50.00 51
-50.00 51
-50.00 51
135 43

So output of rmstranamt should ultimately be 435 if it were to parse through my CASE statement

 

[SQLDenis]

Can this ever be true?
CASE WHEN rmstrancde IN ('50','51','52','53')
AND rmstrancde < 0 THEN
This is the same field (rmstrancde ) right?


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dba112233]

EXACTLY

If it's already negative, keep it negative...

 

[dba112233]

crap, I should have this instead

SELECT RMSFILENUM,
RMSTRANCDE,
SUM(
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt < 0 THEN
rmstranamt
WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt >= 0 THEN
-ABS(rmstranamt)
WHEN rmstrancde IN ('55','56','57','58') THEN
ABS(rmstranamt)
ELSE
rmstranamt
END
) As rmstranamt

I change AND rmstrancde to AND rmstranamt

but still I get the same issue, -265 is returned, not 435

 

[dba112233]

I sorry, I made a mistake explaining, LET'S start over.

If I have incoming values of
rmstranamt rmstrancde
50.00 51
50.00 51
50.00 51
50.00 51
50.00 51
50.00 51
50.00 51 <---- there is an extra 50 now
-50.00 51 <---- there is one lest -50 now
135 43

I should be getting -165 for rmstranamt in the end, not -265 which I'm getting now. Somewhere it's skipping 100

SELECT RMSFILENUM,
RMSTRANCDE,
SUM(
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt < 0 THEN
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

 

[dba112233]

figured it out, the user gave me the incorrect business requirements!!