March 6, 2014 at 3:59 am
Hi
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If i used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
select cast((35622.15/2) as decimal(15,2))
Is there any way i can achieve this.
Thanks
March 6, 2014 at 4:08 am
These are some serious rounding errors and it will not be easy to solve them.
You can add extra precision, but what if you divide 100 by 3? Even float won't help you then.
It seems there's actually a design flaw in your databases, if you have to distribute the amount of joint accounts over the different account holders. You should model this using a bridge table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2014 at 5:06 am
You could always use
select cast((35622.15/2) as decimal(15,2)) AS P1, 35622.15 - cast((35622.15/2) as decimal(15,2)) AS P2
March 6, 2014 at 4:33 pm
If the original amount is odd, add 1 cent to the first joint account holder. That will work regardless of the number of joint account holders:
select cast(original_amount/2 +
case when first_joint_account = 1 and
right(cast(original_amount as decimal(15,2)),1) in ('1','3','5','7','9') then 0.01 else 0.00 end
as decimal(15,2))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 6, 2014 at 5:47 pm
ScottPletcher (3/6/2014)
If the original amount is odd, add 1 cent to the first joint account holder. That will work regardless of the number of joint account holders:select cast(original_amount/2 +
case when first_joint_account = 1 and
right(cast(original_amount as decimal(15,2)),1) in ('1','3','5','7','9') then 0.01 else 0.00 end
as decimal(15,2))
Nice idea!
There's also a more elaborate way that may be more appropriate when not all account holders are alike:
Financial Rounding of Allocations[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply