How to retrieve a number in range that matches another number

Question

How to retrieve a number in range that matches another number

Weegee71

SSChasing Mays

Points: 604
More actions
November 5, 2021 at 5:28 pm

#3948443

Ive got a lookup table with a set of values .
this is as follows
DROP TABLE IF EXISTS [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts]
CREATE TABLE [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts](
[ID] [int] NULL,
[NumbeOfRecords] [decimal](15, 6) NULL,
[NumberOfGroups] [int] NULL,
[TimeInterval] [decimal](15, 6) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (1, CAST(1555201.000000 AS Decimal(15, 6)), 1944, CAST(8000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (2, CAST(777600.000000 AS Decimal(15, 6)), 972, CAST(16000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (3, CAST(388800.000000 AS Decimal(15, 6)), 486, CAST(32000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (4, CAST(194400.000000 AS Decimal(15, 6)), 243, CAST(64000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (5, CAST(97200.000000 AS Decimal(15, 6)), 122, CAST(127475.409836 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (6, CAST(48600.000000 AS Decimal(15, 6)), 61, CAST(254950.819672 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (7, CAST(24300.000000 AS Decimal(15, 6)), 30, CAST(518400.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (8, CAST(12150.000000 AS Decimal(15, 6)), 15, CAST(1036800.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (9, CAST(6075.000000 AS Decimal(15, 6)), 8, CAST(1944000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (10, CAST(3037.000000 AS Decimal(15, 6)), 4, CAST(3888000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (11, CAST(1518.000000 AS Decimal(15, 6)), 2, CAST(7776000.000000 AS Decimal(15, 6)))
GO
INSERT [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts] ([ID], [NumbeOfRecords], [NumberOfGroups], [TimeInterval]) VALUES (12, CAST(759.000000 AS Decimal(15, 6)), 1, CAST(15552000.000000 AS Decimal(15, 6)))
GO
I want to be able to look up the correct value for [TimeInterval] that coresponds to an input paramter
So for example if my input parameter is 691195
Then i could see from my table that this number is greater than the [TimeInterval] at level 7 (518400)
and less than the number at level 8 1036800
Therefore i would like to be able to select level 8
How can i do this? i tried using between hut i dont know exactly which 2 rows, my value will lie within
Attached is a excel spreadsheet showing what i want to represent

Attachments:
You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

Here's one simple way. It'll really help things if you have an index on the TimeInterval column.

DECLARE @InputParameter [decimal](15, 6) = 691195
;
 SELECT TOP 1 *
   FROM [dbo].[SIData_NTILE_Test_20211012_6months_MainLevelData_TableRecordCounts]
  WHERE [TimeInterval] >= @InputParameter
  ORDER BY [TimeInterval]
;

And, thanks for the readily consumable test data that ran correctly the first time. Things like that really help us.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

Did that work for you? "Enquiring minds want to know". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Weegee71 SSChasing Mays Points: 604 More actions · Answer 3

Hi Jeff

Apologies for not replying earlier. I have been on holiday

Yes it worked perfectly . Thank you very much for that.

You mentioned indexing....yes this is something next I want to think about , as we are loading high volume of

data.

How to retrieve a number in range that matches another number

Attachments: