CODEWITHSUNDEEP
sqlsql-server
Yaina Villafañes
Yaina Villafañes

Reputation: 181

SQL: Get all dates between two dates with shift

I have a table that content the column "startbooking" and "endbooking" I have other that content the shift:

id |start  |end
M  | 06:00 |14:00
T  | 14:00 |20:00
N  | 20:00 |06:00

so, i need to get all dates between startbooking and endbooking with the shifts

example:

startbooking: 01/05/2015 12:00  endbooking: 02/05/2015 16:00

result:

01/05/2015 |M
01/05/2015 |T
01/05/2015 |N
02/05/2015 |M
02/05/2015 |T

Upvotes: 0

Views: 304

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Perhaps you will need recursive cte with cross join :

with t as (
     select startdt, enddt
     from table
     union all
     select dateadd(day, 1, startdt), enddt
     from t
     where startdt < enddt
)

select t.startdt, sft.id
from t cross join (select distinct id from shifttable) sft
option (maxrecursion 0);

Here is a Demo.

Upvotes: 3

Related Questions