CODEWITHSUNDEEP
sql-servert-sql
Rzassar
Rzassar

Reputation: 2282

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

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.

Upvotes: 0

Views: 47

Answers (2)

Rzassar
Rzassar

Reputation: 2282

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]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 1

Related Questions