Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jodders
Starting Member
41 Posts |
 Posted - 2013-09-19 : 06:48:51
|
Morning,I am trying to eliminate a rounding issue with my query. It's bring back -0.01 and +0.01 which I don't want it to. I have played around with my having clause but have hit a road block.select....sum(case when acct_type <> 'IN' then sign*(amount*rate) else 0 end) 'A',sum(case when acct_type = 'IN' then sign*(amount*rate) else 0 end) 'D'join.....where...group by...having sum(sign*amount) <> 0 Any help?ThanksJod |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-09-19 : 07:07:25
|
CAST( sign * amount * rate AS INT)
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
jodders
Starting Member
41 Posts |
Posted - 2013-09-19 : 07:37:23
|
| Thanks SwePeso, do i need to add that into the having clause? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-19 : 09:22:39
|
Add to both - in the select clause and in the having clause. You have to decide whether you want to round each individual rows separately, or round only the aggregate. If I had to guess, I would say use the rounding on the aggregate.select....CAST(sum(case when acct_type <> 'IN' then sign*(amount*rate) else 0 end) AS INT) 'A',CAST(sum(case when acct_type = 'IN' then sign*(amount*rate) else 0 end) AS INT) 'D'join.....where...group by...having CAST(sum(sign*amount) AS INT) <> 0 |
|
|
|
|
|
|
|
|
Having clause and my 0.01s!