needing to insert 2 sets of numbers to a table to use as ranges for a query

Question

needing to insert 2 sets of numbers to a table to use as ranges for a query

roy.tollison

SSCommitted

Points: 1930
More actions
July 12, 2024 at 5:20 pm

#4435622
the ranges (a lot) need to have a preset spacing between each set of numbers.
value1, value2
-999999999,-998999999
-998999998,-997999999
-997999998,-996999999
and so on until i reach the limits of +999999999
not a gap/island thing have a query for between those ranges, don't care how long it takes to add the ranges or query the ranges.
- This topic was modified 5 months, 1 week ago by roy.tollison.

Viewing 7 posts - 1 through 6 (of 6 total)

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 1

Did you know that a table has to have a key. By definition. Not as an option. You posted garbage! Why do you think of column names are meaningful? For over 30 years, the standard netiquette on SQL forums has been to post DDL. Why are you exempt?

CREATE TABLE Foobar_Ranges

(foobar1 DECIMAL (11,0) NOT NULL PRIMARY KEY, -- required!

foobar2 DECIMAL (11,0) NOT NULL,

CHECK (foobar2 > foobar1) );

I made guesses as to the key and added constraints to guarantee that you have ranges. But with no other information, these are only guesses. This might be character data or integers, but you call them numbers . You might have more constraints that are implied but not shown here. Frankly, I would use a text editor or word processor since you are generating data. They are pretty good at this suff. You're only going to do it once, so the speed of creation isn't that important.

Can you give us more information about your problem? This just does not feel right. And my guess would be that these ranges could be constructed with the calculation on what ever you are validating or classifying this data.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

Install this TVF.

Then this SQL will do the job

select x.value value1, x.value+1000001 value2
  from dbo.GENERATE_SERIES(-999999999,+999999999,1) x
;

roy.tollison SSCommitted Points: 1930 More actions · Answer 3

ok got table1

t1_Date (Date), t1_Value (bigint), t1_Event (varchar(100))

now my user wanted a way to show count of table1 records where t1_Value (bigint) is between 2 values. Simple enough query no need to see/post. However, my user then asked if he could see the count of table1 records between a bunch of values paired. when i asked for ranges, he, the user, asked if we could make the ranges (table2 in this case) to be variable. ie Something that he determines at any given time. So rather than trying to create one hell of a query to accomplish n ranges, i thought that a range table would be a better solution. So he could dictate the gap between ranges to fit his needs. Now the Range table (table2) has no bearing/relationship to table1 other than a range for the t1_Value. not the Date nor the Event, just the Value. after looking into the MIN(t1_Value) and the Max(t1_Value) I saw that -999,999,999 and +999,999,999 would cover any ranges he desired, he thought 100,000 would be a good gap size. i suggested we start a little bigger and let him then see what his needs might be. If he decides to change the gap, I figured we could just wipe the Range table (Table2) which I figured should only have 2 values (bigint's) to do the gaping and that would then re-add in mass any new gap ranges.

That is the only purpose of the RANGE table. Unless there is a way to make a query to count the records within the gaps and to stop at the hi-end of the gap.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jonathan AC Roberts wrote:

Install this TVF.
Then this SQL will do the job
select x.value value1, x.value+1000001 value2
  from dbo.GENERATE_SERIES(-999999999,+999999999,1) x
;

Jonathan AC Roberts wrote:

Install this TVF.
Then this SQL will do the job
select x.value value1, x.value+1000001 value2
  from dbo.GENERATE_SERIES(-999999999,+999999999,1) x
;

The first 3 rows out of that don't appear to be close to what the OP outlined in his post. It looks like the "step" is incorrect.

--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)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Install this TVF.
Then this SQL will do the job
select x.value value1, x.value+1000001 value2
  from dbo.GENERATE_SERIES(-999999999,+999999999,1) x
;
The first 3 rows out of that don't appear to be close to what the OP outlined in his post. It looks like the "step" is incorrect.

Yes, the preset spacing in the OP's example changes from 1000000 in the first row to 999999 in the next and then 999999 again

So not sure what the OP wants, there is not enough information in the question to work out what they want.

schleep SSChampion Points: 12585 More actions · Answer 6

This should get you started

DECLARE @RangeINTEGER = 1000000
DECLARE @RowsINTEGER = (2000000000 / @Range) + 1
--SELECT @nRows
;WITH Num AS-- This will work for ranges > 172. Add another cross join for greater granularity.
(SELECTTOP (@Rows) nRow = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROMsys.objectso1
CROSS JOIN sys.objectso2
CROSS JOIN sys.columnsc3
ORDER BY 1),
base AS (SELECTValue1 = (-1000000000 + ((n.nRow -1) * @Range) + 1)
FROMNumn)
SELECTValue1,
Value2 = LEAD(Value1, 1) OVER (ORDER BY Value1 ASC)
FROMBase