June 17, 2011 at 11:13 am
Oracle SQL:
SELECT (NVL(v1.non_credit,0) - NVL(v2.credit,0)) settle_amt from
(SELECT sum((trans_amount)/100) non_credit from rita.request
where (trans_amount IS NOT NULL) and status_code = 4 and (command <> 'CREDIT')) v1,
(select sum((trans_amount)/100) credit from rita.request
where (trans_amount IS NOT NULL) and status_code = 4 and command = 'CREDIT') v2
/
trans_amount varchar 12(null)
settle_amt results would be: 4.00 showing decimal places
SQL 2008:
SELECT (ISNULL(v1.non_credit,0) - ISNULL(v2.credit,0)) settle_amt from
(SELECT sum((trans_amount)/100) non_credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and (command <> 'CREDIT')) v1,
(select sum((trans_amount)/100) credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and command = 'CREDIT') v2
go
trans_amount varchar 12(null)
settle_amt result would be: 4
I need the settle_amt to be 4.00 in SQL 2008
June 17, 2011 at 1:25 pm
Without table structure there may be syntax issues. But the code below should work, and be noticeably faster.
SELECT CAST(SUM(trans_amount)/100.00 AS DECIMAL(10,2) settle_amt
FROM
(
SELECT CASE command = 'Credit' then isnull(trans_amount,0) ELSE -1* isnull(trans_amount,0) END trans_amount
FROM dbo.request
WHERE trans_amount IS NOT NULL AND status_code = 4
) myData
good luck
Daryl
June 17, 2011 at 1:30 pm
Thanks Daryl
We tried this but it looks like it truncated or rounded down:
SELECT (ISNULL(v1.non_credit,0) - ISNULL(v2.credit,0)) from
(SELECT CONVERT(smallmoney, sum((trans_amount)/100), 0) non_credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and (command <> 'CREDIT')) v1,
(SELECT CONVERT(smallmoney,sum((trans_amount)/100), 0) credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and command = 'CREDIT') v2;
go
We should have received this: 23.70 instead we received 23.00
June 17, 2011 at 1:33 pm
I think this line is causing your issue
CONVERT(smallmoney, sum((trans_amount)/100), 0)
try
CONVERT(smallmoney, sum((trans_amount)/100.00), 0)
The value is rounding on the divide before you convert.
BTW: you didn't like the rewrite?
Daryl
June 17, 2011 at 1:36 pm
Daryl, I hadn't tried it yet. I just ran my SQL and got the results when I also received a post from you. It was a timing thing.
I will try it though for sure.
June 20, 2011 at 1:43 pm
Daryl, I tried (SELECT CONVERT(smallmoney, sum((trans_amount)/100.00), 0), I received this error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
Then I tried your sql
SELECT CAST(SUM(trans_amount)/100.00 AS DECIMAL(10,2) settle_amt
FROM
(
SELECT CASE command = 'Credit' then isnull(trans_amount,0) ELSE -1* isnull(trans_amount,0) END trans_amount
FROM dbo.request
WHERE trans_amount IS NOT NULL AND status_code = 4
) myData
I received these errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'settle_amt'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'trans_amount'.
June 20, 2011 at 2:09 pm
Not sure why smallMoney is failing, however if you replace smallmoney with decimal (10,2) the results look good.
CONVERT(DECIMAL(10,2), sum((trans_amount)/100.00), 0)
The syntax error on the bottom was a missing paren and missing WHEN in the case statement.
SELECT CAST(SUM(trans_amount)/100.00 AS DECIMAL(10,2)) settle_amt
FROM
(
SELECT CASE WHEN command = 'Credit' then isnull(trans_amount,0) ELSE -1* isnull(trans_amount,0) END trans_amount
FROM dbo.request
WHERE trans_amount IS NOT NULL --AND status_code = 4
) myData
June 20, 2011 at 2:37 pm
Hey Daryl,
Glad you are here.
I'm not a SQL expert yet! 🙂
I ran our old sql with the change you suggested:
SELECT (ISNULL(v1.non_credit,0) - ISNULL(v2.credit,0)) from
(SELECT CONVERT(DECIMAL(10,2), sum((trans_amount/100.00)), 0) non_credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and (command <> 'CREDIT')) v1,
(SELECT CONVERT(DECIMAL(10,2), sum((trans_amount/100.00)), 0) credit from dbo.request
where (trans_amount IS NOT NULL) and status_code = 4 and command = 'CREDIT') v2;
I received the overflow error again. 🙁
I ran your sql with the changes you suggested:
SELECT CAST(SUM(trans_amount)/100.00 AS DECIMAL(10,2)) settle_amt
FROM
(
SELECT CASE WHEN command = 'Credit' then isnull(trans_amount,0) ELSE -1* isnull(trans_amount,0) END trans_amount
FROM dbo.request
WHERE trans_amount IS NOT NULL AND status_code = 4
) myData
I received the correct amount but it was negative amount: -35.45. It should be 35.45.
I wasn't sure what the -1* did.
The sql is supposed to select all the "command=credit" tran amounts that have a status type = 4 and total them up.
The sql is supposed to select all the "command not = credit" trans amounts that have a status type = 4 and total them up.
Then the sql is to subtract the credit amount total from the non-credit amount total to come up with the answer.
June 20, 2011 at 2:43 pm
The case statement is reversed.
is
SELECT CASE WHEN command = 'Credit' then isnull(trans_amount,0) ELSE -1*
should be
SELECT CASE WHEN command != 'Credit' then isnull(trans_amount,0) ELSE -1*
June 20, 2011 at 2:51 pm
That was it! It's working. Awesome! :hehe:
You never did answer what the -1* did. I think what it is doing is multiplying trans_amount by -1 to make it a negative amount. Am I right! 😀
Patti
June 20, 2011 at 2:57 pm
Yes,
Instead of doing two sums and then subtracting, the case is setting the credit values to negative and then summing all.
June 20, 2011 at 3:01 pm
Okay. Thanks again Daryl. Have a great day!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply