Price rounding (store prices) !!!

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Price rounding (store prices) !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-28 : 09:27:13

Hi All,

I have a following field in a table.
Price (decimal(18,2))

In my select I want to round this field & return it.
I want to round it in a way that we usually see prices in any grocery store.

For example usually we dont see price of $1.67, $11.98, $10.86, $3.09. Instead we usually see $1.75, $11.99, $10.99, $3.10 respectively.

I want the last 2 decimal placing of my roundings to something like this.

.00
.10
.20
.25
.30
.35
.40
.50
.60
.65
.75
.85
.99

etc

Is there a built in function to do that?
If yes, how.

Thanks for all your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:31:18

To what rounding should a value of x.05 go to?
0.00 or 0.10?

Please provide your business rules. it is very hard for us to work with you elsewise.

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-28 : 09:40:20

How does 1.67 go to 1.75 and 3.09 not go to 3.00? Also if 1.67 goes to 1.75 then 10.86 should round to 10.75, 11.98 to 12.00. It's like you have specific rounding rules for each individual number.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-28 : 09:40:41

Peso,

X.05 should round to 0.10. Meaning after the rounding the Price either should remain same (if its already rounded) or it should increase to achieve the nearest rounding in the following list.

.00
.10
.20
.25
.30
.35
.40
.50
.60
.65
.75
.85
.99

Thanks in advance.....

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-28 : 09:42:38

That's some kind of custom rounding routine and I bet you'd have to write a function or custion sql to handle it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 09:44:03

Something like this


select d from
(
	select cast('1.75' as decimal(12,2)) as d union all
	select '1.85' as d union all
	select '1.99' as d union all
	select '1.76' as d 
) t
where cast(d*100 as int)%100 in (75,85,99)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 10:02:13

[code]-- Prepare sample data
DECLARE @Sample TABLE (Number DECIMAL(4, 2))

INSERT @Sample
SELECT Number / 100.0 + ABS(CHECKSUM(NEWID())) % 23
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 0 AND 99

DECLARE @Rounding TABLE (d DECIMAL(3, 2))

INSERT @Rounding
SELECT .00 UNION ALL
SELECT .10 UNION ALL
SELECT .20 UNION ALL
SELECT .25 UNION ALL
SELECT .30 UNION ALL
SELECT .35 UNION ALL
SELECT .40 UNION ALL
SELECT .50 UNION ALL
SELECT .60 UNION ALL
SELECT .65 UNION ALL
SELECT .75 UNION ALL
SELECT .85 UNION ALL
SELECT .99

-- Show the expected output
SELECT s.Number,
(SELECT MIN(d) FROM @Rounding AS r WHERE r.d >= s.Number - FLOOR(s.Number))
FROM @Sample AS s
ORDER BY s.Number[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources