January 12, 2006 at 3:23 pm
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
-50.00 51
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
I tried to incorporate DISTINCT:
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt < 0 THEN
DISTINCT rmstranamt
WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt >= 0 THEN
-ABS(DISTINCT rmstranamt)
WHEN rmstrancde IN ('55','56','57','58') THEN
ABS(DISTINCT rmstranamt)
WHEN rmstrancde NOT IN ('50','51','52','53','55','56','57','58') THEN
DISTINCT rmstranamt
ELSE
DISTINCT rmstranamt
END
) As rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
But get this error:
Msg 156, Level 15, State 1, Line 58
Incorrect syntax near the keyword 'DISTINCT'.
January 12, 2006 at 3:46 pm
>>I should be getting -165 for rmstranamt in the end, not -265 which I'm getting now. Somewhere it's skipping 100
No-one here can assist until you explain why.
According to your query logic, -265 is the correct answer, so you need to explain why it should be -165.
If code = 51 and amount is >= zero, you want negative ABS() amount. You have 7 records like this. that makes 7 * -50 = -350
If code = 51 and amount < zero, you want the amount, unaltered. That's (-350 + -50) = -400.
Then you have 1 record code = 43, so you want to add that amount unaltered.
(-400 + 135) = -265
Why should it be -165 ?
January 12, 2006 at 3:55 pm
>>>>If code = 51 and amount is >= zero, you want negative ABS() amount. You have 7 records like this. that makes 7 * -50 = -350
no, there's 7 50s and one -50
What I epect is the -350 to be flipped because the business rule is if the value coming in is positive and has a code between 50 - 53, then change it to be negative.
On the other hand, if the value coming in is negative and has a code between 50-53 keep it negative
any code outside those ranges should just be treated as is. So the 135 has a code of 43, so just sum 135
If the code coming in has a code of 55-53, change it to positive
so with that, I should get after the checks this:
-350 + 50 = -300
-300 + 135 = -165
that is what I expect, maybe my code is not doing that but I though it was
January 12, 2006 at 4:03 pm
found it, it wasn't my code, it was the business logic the person gave me
should have been:
CASE WHEN rmstrancde IN ('50','51','52','53') AND rmstranamt < 0 THEN
ABS(rmstranamt)
January 12, 2006 at 4:05 pm
Fair enough, but you see the general point ? You can't post a whole whack of code and say "it's wrong" without posting what the requirements are and what the expected result is supposed to be.
January 12, 2006 at 5:16 pm
yep, I thought I gave enough info, next time I'll prepare the thread better. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply