Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SUM not working

Status
Not open for further replies.

dba112233

Programmer
Jan 10, 2006
39
US
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
 
EXACTLY

If it's already negative, keep it negative...
 
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
 
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
 
figured it out, the user gave me the incorrect business requirements!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top