How to compare two values of same column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to compare two values of same column?

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-28 : 00:27:36

Hi,

In my stored proc , i m passing @Range as my input.

In my Table i have column called "Range"

and the data looks as below for Range column
5
10
15
Now i f i give @Range as 6 ,then it should return me the second row (@Range>5 AND @Range<10)

So can anyone tell me , how can i acheie this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 00:41:45

sorry didnt understand that how will second row qualify this condition?
As i see , its having value as 10 which will not satisfy (@Range>5 AND @Range<10)

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-28 : 00:45:16

As i mentioned above my @Range variable has , lets say 6
now 6>5, and i dont want first record
my 6<10, and i need to return this
(6>5 && 6<10)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 00:53:36

do you mena this then?


SELECT TOP 1 *
FROM YourTable
WHERE RangeColumn >=@Range
ORDER BY RangeColumn

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-28 : 01:09:00

Not Exactly...
Below is my Actual query

SELECT VALIDATIONCDE,TERM FROM
(SELECT * FROM dbo.dbo_TJ666F WHERE ITEMITEM = @ComponentCd AND VALIDFLAG= 1 AND
(SELECT CONVERT(VARCHAR(8),@RCD , 112)) BETWEEN ITMFRM AND ITMTO) LIMITS

UNPIVOT(TERM FOR TERMRANGE IN(ZRTERM01,ZRTERM02,ZRTERM03,ZRTERM04,
ZRTERM05,ZRTERM06,ZRTERM07,ZRTERM08,ZRTERM09,ZRTERM10)) AS TERM

UNPIVOT(VALIDATIONCDE FOR VALIDATION_CODE IN(ZVALCDE01,ZVALCDE02,ZVALCDE03,ZVALCDE04,
ZVALCDE05,ZVALCDE06,ZVALCDE07,ZVALCDE08,ZVALCDE09,ZVALCDE10)) AS VALIDATION_CODE

WHERE RIGHT(TERMRANGE,2)= RIGHT(VALIDATION_CODE,2)

When i execute this i will get the following result set

CODE Range
----- -------
FWRBA 5
FWRBB 10
FWRBC 15
FWRBD 99

Now i have to fetch code FWRBB when my Range is 6 bcoz it is <10, if my range is 12
i should fetch 3rd record

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 01:16:02

[code]
SELECT VALIDATIONCDE,TERM
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY TERM) AS Rn,VALIDATIONCDE,TERM FROM
(SELECT * FROM dbo.dbo_TJ666F WHERE ITEMITEM = @ComponentCd AND VALIDFLAG= 1 AND
(SELECT CONVERT(VARCHAR(8),@RCD , 112)) BETWEEN ITMFRM AND ITMTO) LIMITS

UNPIVOT(TERM FOR TERMRANGE IN(ZRTERM01,ZRTERM02,ZRTERM03,ZRTERM04,
ZRTERM05,ZRTERM06,ZRTERM07,ZRTERM08,ZRTERM09,ZRTERM10)) AS TERM

UNPIVOT(VALIDATIONCDE FOR VALIDATION_CODE IN(ZVALCDE01,ZVALCDE02,ZVALCDE03,ZVALCDE04,
ZVALCDE05,ZVALCDE06,ZVALCDE07,ZVALCDE08,ZVALCDE09,ZVALCDE10)) AS VALIDATION_CODE

WHERE RIGHT(TERMRANGE,2)= RIGHT(VALIDATION_CODE,2)
)t
WHERE Rn=1
[/code]

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-28 : 01:22:13

you are really great!!!
Thanks for all ur help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 01:45:27

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources