December 3, 2008 at 6:47 am
Hello all -
I hope someone out there can help me. I need to create a function which rounds a number up to the nearest half dollar. Therefore, both 1.21 and 1.44 would round up to 1.50. In theory this seems like a no-brainer. Unfortunately, my brain has taken an early holiday;( Any thoughts?
December 3, 2008 at 6:58 am
Something like this would work:
Case
When amount - Convert(int, amount) <= 0.5 then Convert(int, amount) + 0.50
Else Convert(int, amount) + 1.00
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2008 at 7:00 am
Edit: Didn't read the round up. Recalculating....
Edit 2: This one works.
declare @tbl table (Val numeric (8,2))
insert into @tbl values (0) -- to 0
insert into @tbl values (1.21) -- to 1.5
insert into @tbl values (1.44) -- to 1.5
insert into @tbl values (1.49) -- to 1.5
insert into @tbl values (1.5) -- to 1.5
insert into @tbl values (1.65) -- to 2
insert into @tbl values (1.81) -- to 2
insert into @tbl values (2) -- to 2
select CEILING((val)*2)/2 from @tbl
Multiply value by 2, round up to nearest integer, divide result by 2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2008 at 7:08 am
GilaMonster (12/3/2008)
Edit: Didn't read the round up. Recalculating....
Waiting to see how you solve this one, Gail. I'm sure it will be better than mine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2008 at 7:10 am
THANK YOU, THANK YOU - works like a charm. I knew it could be done in a few lines of code - just didn't know what they were.
December 3, 2008 at 7:25 am
Use Gail's solution. Mine didn't handle values with .00 correctly.
Not to mention hers is cooler.:P
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2008 at 7:31 am
Very nice. I would have cased it as well, but a nice solution, Gail.
December 3, 2008 at 7:35 am
Jack Corbett (12/3/2008)
Use Gail's solution. Mine didn't handle values with .00 correctly.
Mine had that problem too, before I remembered about Ceiling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply