PicoDeGallo PicoDeGallo - 5 months ago 17
SQL Question

Create Set of IDs based on Varying ID Ranges

I have a collection of tables that have missing document ID ranges. The

Start Range
is the beginning ID, the
End Range
is the ending ID, and the
Missing
is the number of rows within that range that are missing (includes the beginning and end ID within its count). I was wondering how I can go about parsing into a new table the individual IDs as opposed to the actual range based on the
Missing
column.

This is how the data is presented:

+-------------+-----------+---------+-----------+
| Start Range | End Range | Missing | Date |
+-------------+-----------+---------+-----------+
| 184 | 186 | 3 | 1/9/1979 |
| 204 | 207 | 4 | 1/9/1979 |
| 209 | 212 | 4 | 1/9/1979 |
| 223 | 224 | 2 | 1/9/1979 |
| 240 | 241 | 2 | 1/10/1979 |
| 243 | 243 | 1 | 1/10/1979 |
| 248 | 249 | 2 | 1/10/1979 |
| 261 | 265 | 5 | 1/11/1979 |
+-------------+-----------+---------+-----------+


I am looking to acheive output as such:

+-----+-----------+
| ID | Date |
+-----+-----------+
| 184 | 1/9/1979 |
| 185 | 1/9/1979 |
| 186 | 1/9/1979 |
| 204 | 1/9/1979 |
| 205 | 1/9/1979 |
| 206 | 1/9/1979 |
| 207 | 1/9/1979 |
| 209 | 1/9/1979 |
| 210 | 1/9/1979 |
| 211 | 1/9/1979 |
| 212 | 1/9/1979 |
| 223 | 1/9/1979 |
| 224 | 1/9/1979 |
| 240 | 1/10/1979 |
| 241 | 1/10/1979 |
| 243 | 1/10/1979 |
| 248 | 1/10/1979 |
| 249 | 1/10/1979 |
| 261 | 1/11/1979 |
| 262 | 1/11/1979 |
| 263 | 1/11/1979 |
| 264 | 1/11/1979 |
| 265 | 1/11/1979 |
+-----+-----------+


What would be the best method to achieve this? Thanks for the assistance.

Answer

I use a UDF to generate ranges, but a numbers table or tally table would do the trick as well

Declare @Table table (StartRange int,EndRange int,Date Date)
Insert into @Table values
(184,186,'1979-01-09'),
(204,207,'1979-01-09')


Select B.ID
      ,A.Date
 From @Table A
 Join (Select ID=cast(RetVal as int) from [dbo].[udf-Create-Range-Number](1,9999,1)) B
   on B.ID between A.StartRange and A.EndRange
 Order by B.ID,Date

Returns

ID  Date
184 1979-01-09
185 1979-01-09
186 1979-01-09
204 1979-01-09
205 1979-01-09
206 1979-01-09
207 1979-01-09

The UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Number] (@R1 money,@R2 money,@Incr money)

-- Syntax Select * from [dbo].[udf-Create-Range-Number](0,100,2)

Returns 
@ReturnVal Table (RetVal money)

As
Begin
    With NumbTable as (
        Select NumbFrom = @R1
        union all
        Select nf.NumbFrom + @Incr
        From NumbTable nf
        Where nf.NumbFrom < @R2
    )
    Insert into @ReturnVal(RetVal)

    Select NumbFrom from NumbTable Option (maxrecursion 32767)

    Return
End