Generate a row range based on "FromRange" "ToRange" field of each row

Question

I have a table with following fields:
DailyWork(ID, WorkerID, FromHour, ToHour) assume that, all of the fields are of type INT.
This table needs to be expanded in a T_SQL statement to be part of a JOIN.
By expand a row, I mean, generate a hour for each number in range of FromHour and ToHour. and then join it with the rest of the statement.

Example:
Assume, I have another table like this: Worker(ID, Name). and a simple SELECT statement would be like this:

SELECT * FROM
Worker JOIN DailyWork ON Worker.ID = DailyWork.WorkerID

The result has columns similar to this: WorkerID, Name, DailyWorkID, WorkerID, FromHour, ToHour
But, what i need, has columns like this: WorkerID, Name, Hour.
In fact the range of FromHour and ToHour is expanded. and each individual hour placed in separate row, in Hour column.
Although i read a similar question to generate a range of number , but it didn't really help.

score 1 · Answer 1 · answered Apr 16 '16 at 11:42

I you start with a list of numbers, then this is pretty easy. Often, the table master.spt_values is used for this purpose:

with nums as (
      select row_number() over (order by (select null)) - 1 as n
      from master.spt_values
     )
select dw.*, (dw.fromhour + nums.n) as specifichour
from dailywork dw join
     nums
     on dw.tohour >= dw.fromhour + nums.n;

The table master.spt_values generally has a few thousand rows at least.

score 0 · Accepted Answer · answered Apr 17 '16 at 04:03

Another solution would be...

WITH [DayHours] AS (
    SELECT 1 AS [DayHour]
    UNION ALL
    SELECT [DayHour] + 1 FROM [DayHours] WHERE [DayHour] + 1 <= 24
)
SELECT [Worker] 
JOIN [DayHours] ON [Worker].[FromHour] <= [DayHours].[DayHour]
            AND [Worker].[ToHour] >= [DayHours].[DayHour]

Generate a row range based on "FromRange" "ToRange" field of each row

2 Answers2