Range - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Range

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 09:45:50

How do i round a float value to its previous and next tenth place?

Example:

1.34
Previous 10th : 0
Next 10th: 10

45.67
Previous 10th : 40
Next 10th: 50

Basically I am trying to generate range for my FLOAT Column

1.34
Req o/p: Between 0 and 10

45.67
Req o/p: Between 40 and 50

Thanks,

Prakash.P

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:46:47

Use modula operator %.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:49:40

Or simpler FLOOR and CEILING

DECLARE	@Sample TABLE (f FLOAT)

INSERT	@Sample
SELECT	1.34 UNION ALL
SELECT	114.78 UNION ALL
SELECT	-31.4159 UNION ALL
SELECT	45.67

SELECT	f,
	CAST(FLOOR(f / 10) AS INT) * 10 AS LowerLimit,
	CAST(CEILING(f / 10) AS INT) * 10 AS HigherLimit
FROM	@Sample

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

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 10:35:14

But I might have interger values like 10, 20 also in my column.

for 10 - I need 0 to be lowerlimit and 10 to be upper limit.

Your query will give 10 for both upper an dlower limt?

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 10:45:38

A simple rewrite gives

DECLARE	@Sample TABLE (f FLOAT)

INSERT	@Sample
SELECT	1.34 UNION ALL
SELECT	114.78 UNION ALL
SELECT	10 UNION ALL
SELECT	-20 UNION ALL
SELECT	-31.4159 UNION ALL
SELECT	45.67

SELECT	f,
	CASE
		WHEN LowerLimit = HigherLimit AND f >= 0.0 THEN LowerLimit - 10
		ELSE LowerLimit
	END AS LowerLimit,
	CASE
		WHEN LowerLimit = HigherLimit AND f < 0.0 THEN HigherLimit + 10
		ELSE HigherLimit
	END AS HigherLimit
FROM	(
		SELECT	f,
			CAST(FLOOR(f / 10.0) AS INT) * 10 AS LowerLimit,
			CAST(CEILING(f / 10.0) AS INT) * 10 AS HigherLimit
		FROM	@Sample
	) AS d

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