Need Help!! - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help!!

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-19 : 03:59:52

I have my Data as shown below:
ITEMITEM ISSAGE_FRM ISSAGE_TO CESSAGE_FRM CESSAGE_TO

E10RINR 0 60 10 10
E10RINR 0 0 15 15
E10RINR 0 0 20 20
E10RINR 0 0 25 25

Here CESSAGE is the input
If suppose my CESSAGE input is 20, i have to return ISSAGE_FRM and ISSAGE_TO corresponding to that Range.. IF ISSAGE_FRM and ISSAGE_TO record is null or Empty, i should return first record of ISSAGE_FRM, ISSAGE_TO (0-60)..
Can any one help quicky??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 04:42:49

[code]
SELECT TOP 1 E10RINR,ISSAGE_FRM ,ISSAGE_TO
FROM
(
SELECT E10RINR,ISSAGE_FRM ,ISSAGE_TO,1 AS Cat
FROM Table t
WHERE @Input BETWEEN CESSAGE_FRM AND CESSAGE_TO
UNION ALL
SELECT *,2
FROM
(
SELECT TOP 1 E10RINR,ISSAGE_FRM ,ISSAGE_TO
FROM Table
ORDER BY ISSAGE_FRM ,ISSAGE_TO
)r
)m
ORDER BY Cat
[/code]

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

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-19 : 06:25:22

Hi Visakh16,

I have tried ur query ... but it is not gving me results exactly i need.

I gave input 20 and got the ISSAGE_FRM and ISS_AGE_TO as 0, 0,
But what i need is if the values on those rows are 0, i need to get the first record , i.e 0 and 60 record..
can u pls help to get tat desired result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 06:29:03

quote:
Originally posted by Sql_forum

Hi Visakh16,

I have tried ur query ... but it is not gving me results exactly i need.

I gave input 20 and got the ISSAGE_FRM and ISS_AGE_TO as 0, 0,
But what i need is if the values on those rows are 0, i need to get the first record , i.e 0 and 60 record..
can u pls help to get tat desired result

this is different from what you asked earlier. suddenly you requirement has changed to 0 values from empty.
in that case you need to do like this


SELECT TOP 1 E10RINR,ISSAGE_FRM ,ISSAGE_TO
FROM
(
SELECT E10RINR,ISSAGE_FRM ,ISSAGE_TO,1 AS Cat
FROM Table t
WHERE @Input BETWEEN CESSAGE_FRM AND CESSAGE_TO
AND ISSAGE_FRM >0 AND ISSAGE_TO>0
UNION ALL
SELECT *,2
FROM
(
SELECT TOP 1 E10RINR,ISSAGE_FRM ,ISSAGE_TO
FROM Table
ORDER BY ISSAGE_FRM ,ISSAGE_TO 
)r
)m
ORDER BY Cat

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources