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
 Date Range

Author  Topic 

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2013-04-23 : 10:46:17
ID Point Number sla_id From To
1 0 0 1 95.000 NULL
2 150 1 1 90.000 94.999
3 250 2 1 85.001 89.999
4 400 3 1 NULL 85.000

I have the above table structure.
If @myvalue = 95.000 it should return Row ID 1
If @myvalue = 94.999 it should return Row ID 2
If @myvalue = 84.999 it should return Row ID 4
If @myvalue = 85.999 it should return Row ID 4

Here is my query below, I just can't get it right. Please help

DECLARE @myValue decimal(18,3)
DECLARE @max decimal(18,3)
DECLARE @min decimal(18,3)
SET @myValue = 94.999

SELECT TOP 1 [ServiceCreditPointID]
,[ServiceCreditPoint]
,[Number]
,[sla_id]
,[From]
,[To]
FROM [RSA_PM].[dbo].[tb_service_credit_point]
--(@myValue >= ISNULL(@min,From)) and (@myValue <= ISNULL(@max,To))
WHERE
@myValue >= ISNULL(@min,From) AND @myValue <= ISNULL(@max,To)
OR
@myValue >= ISNULL(@min,From) AND @myValue <= ISNULL(@max,To)



If it is that easy, everybody will be doing it

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 10:52:09
quote:
Originally posted by OBINNA_EKE

ID Point Number sla_id From To
1 0 0 1 95.000 NULL
2 150 1 1 90.000 94.999
3 250 2 1 85.001 89.999
4 400 3 1 NULL 85.000

I have the above table structure.
If @myvalue = 95.000 it should return Row ID 1
If @myvalue = 94.999 it should return Row ID 2
If @myvalue = 84.999 it should return Row ID 4
If @myvalue = 85.999 it should return Row ID 4

Here is my query below, I just can't get it right. Please help

DECLARE @myValue decimal(18,3)
DECLARE @max decimal(18,3)
DECLARE @min decimal(18,3)
SET @myValue = 94.999

SELECT TOP 1 [ServiceCreditPointID]
,[ServiceCreditPoint]
,[Number]
,[sla_id]
,[From]
,[To]
FROM [RSA_PM].[dbo].[tb_service_credit_point]
--(@myValue >= ISNULL(@min,From)) and (@myValue <= ISNULL(@max,To))
WHERE
@myValue >= ISNULL(@min,From) AND @myValue <= ISNULL(@max,To)
OR
@myValue >= ISNULL(@min,From) AND @myValue <= ISNULL(@max,To)



If it is that easy, everybody will be doing it

I thought I understood your requirement, except for the case where when @myvalue=85.9999. In that case, you want row 4 to be returned. What is the logic you are using to decide on row 4 in that case, given that 85.999 is less than the To value for row 4?
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2013-04-23 : 11:05:01
Sorry, it was a typo. Can you help pls?

If it is that easy, everybody will be doing it
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 11:38:48
Change the where clause like shown below
select [ServiceCreditPointID]
,[ServiceCreditPoint]
,[Number]
,[sla_id]
,[From]
,[To]
FROM [RSA_PM].[dbo].[tb_service_credit_point]
where
@myValue >= coalesce([From],0) and @myValue <= coalesce([To],1.0e6);
I just randomly picked 0 and a large number as the bounds. If that is not the case, just change them.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2013-04-24 : 04:03:56
Thank you very much James, you made my day

If it is that easy, everybody will be doing it
Go to Top of Page
   

- Advertisement -