CODEWITHSUNDEEP
sqlsql-server
Triforce711
Triforce711

Reputation: 53

Building a dynamic range of numbers based on values in another table

I have a temporary table with the following fields:

I have another table (DaysLate) that is setup with one of the following fields:

Some example values in the (DaysLate) table are 10, 20, 30, 50

I want to insert into the temp table I created a built range of numbers based on the values of the DaysLate table.

For example: If we use the values above, the first record in this temp table would be

The next record would be:

And the next:

Following this pattern to the final value:

The tricky part is that the numbers are all dynamic. There could be only one value in here, 10 values, or even no values. If there aren't any records in the table, I have another route of logic to run.

Upvotes: 1

Views: 253

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17136

you can use a query like below

See live demo

; with indexedDL as 
(
    select *, 
     rn= row_number() over(order by Dayslate asc) 
    from Dayslate
    )

select 
    RangeStart=isnull(d2.Dayslate,0),
    RangeEnd  =d1.Dayslate-1,
    RangeText = isnull(cast(d2.Dayslate as varchar(100)),'0') + isnull('-'+ cast(d1.Dayslate-1 as varchar(100)),'+')
from
    indexedDL d1 full outer join
    indexedDL d2 
       on d1.rn=d2.rn+1

Upvotes: 1

Related Questions