Get consective lower and upper record

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 2008 Forums

Transact-SQL (2008)

Get consective lower and upper record

Author

Topic

vjs2445
Starting Member

16 Posts

Posted - 2011-12-15 : 10:45:40

I have field values 12, 24, 36, 48, 60 in one of my table and need some help in sql statement.

if I pass 36, then I should get 24 & 48.
if I pass 48, then I should get 36 & 60.

Thanks for help

vjs2445
Starting Member

16 Posts

Posted - 2011-12-15 : 10:50:28

I am sorry for previous question which wasn't right.

but here is right one

if I pass any number between a range such as 24 - 36, then I should get 24 & 36.

For example
if I pass 26, then I should get 24 & 36.
if I pass 37, then I should get 36 & 48.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-15 : 10:53:59

You can use the max and min functions like this:

DECLARE @x INT; SET @x = 30;

SELECT 
	MIN(CASE WHEN col1 >= @x THEN col1 END) AS NextN,
	MAX(CASE WHEN col1 < @x THEN col1 END) AS PrevN
FROM
	YourTable;

You will need to use a >= or <= on one or the other, depending on what you want to happen if you happen to send a number that is exactly equal to one of the numbers in the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:54:33

do you have a table that stores range details? ie start and end values for the range?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-15 : 10:56:34

This will give you the lower bound, which you can always add 12 to
DECLARE @int int = 59
select @int = @int - (@int % 12)

select @int

Jim

Everyday I learn something that somebody else already knew

vjs2445
Starting Member

16 Posts

Posted - 2011-12-15 : 11:02:14

Guys Thanks for your help.

I am all set with the help from sunitabeck. Thanks visakh16 and jimf for your help also.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources