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
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