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.
Author |
Topic |
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2013-04-23 : 10:46:17
|
ID Point Number sla_id From To1 0 0 1 95.000 NULL2 150 1 1 90.000 94.9993 250 2 1 85.001 89.9994 400 3 1 NULL 85.000I have the above table structure.If @myvalue = 95.000 it should return Row ID 1If @myvalue = 94.999 it should return Row ID 2If @myvalue = 84.999 it should return Row ID 4If @myvalue = 85.999 it should return Row ID 4Here is my query below, I just can't get it right. Please helpDECLARE @myValue decimal(18,3)DECLARE @max decimal(18,3)DECLARE @min decimal(18,3)SET @myValue = 94.999SELECT 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 To1 0 0 1 95.000 NULL2 150 1 1 90.000 94.9993 250 2 1 85.001 89.9994 400 3 1 NULL 85.000I have the above table structure.If @myvalue = 95.000 it should return Row ID 1If @myvalue = 94.999 it should return Row ID 2If @myvalue = 84.999 it should return Row ID 4If @myvalue = 85.999 it should return Row ID 4Here is my query below, I just can't get it right. Please helpDECLARE @myValue decimal(18,3)DECLARE @max decimal(18,3)DECLARE @min decimal(18,3)SET @myValue = 94.999SELECT 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? |
|
|
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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-23 : 11:38:48
|
Change the where clause like shown belowselect [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. |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2013-04-24 : 04:03:56
|
Thank you very much James, you made my dayIf it is that easy, everybody will be doing it |
|
|
|
|
|
|
|