SQL Server generate data using some data in table

Question

Suppose that we have a table in SQL Server that stores shifts for doctors, so instead of storing explicit dates we are using weekdays, the table looks like this

ShiftId  Day    DoctorId    FromTime      ToTime
--------------------------------------------------
1        SUN    1           08:00:00      16:00:00
2        MON    1           09:00:00.00   14:00:00
3        TUE    1           09:00:00.00   15:00:00
4        WED    1           10:00:00.00   17:00:00
5        THU    1           13:00:00.00   18:00:00

I want to create a select statement to generate explicit dates by using data stored in this table

Example

suppose I want to generate dates between Sunday 19th February 2017 to Tuesday 28th February 2017, the output should be like this

DoctorId    Date                Day     FromTime    ToTime
------------------------------------------------------------
1           '02-19-2017'        SUN     08:00:00    16:00:00
1           '02-20-2017'        MON     09:00:00    14:00:00
1           '02-21-2017'        TUE     09:00:00    15:00:00
1           '02-22-2017'        WED     10:00:00    17:00:00
1           '02-23-2017'        THU     13:00:00    18:00:00
1           '02-26-2017'        SUN     08:00:00    16:00:00
1           '02-27-2017'        MON     09:00:00    14:00:00
1           '02-28-2017'        TUE     09:00:00    15:00:00

Explanation

Times generated for these dates correspond to days stored in our table for example the time generated for '02-19-2017' in the first row is 08:00:00 16:00:00 because '02-19-2017' is Sunday, and the time generated for '02-20-2017' in the second row is 09:00:00 14:00:00 because '02-20-2017' is Monday and so on.

As you may notice no dates generated for 24th February FRI and 25th February SAT, because we don't store Friday and Saturday in our table

Can we write a query in T-SQL that returns this result?

Answer 1

I would use your From/To input parameters to generate a calendar on the fly in a recursive CTE, then join that to your shift table based on the weekday value.

DECLARE @Shift TABLE (ShiftID INT, [Day] VARCHAR(3), DoctorId INT, FromTime TIME, ToTime TIME)
INSERT INTO @Shift 
VALUES (1,'SUN',1,'08:00:00','16:00:00'),
       (2,'MON',1,'09:00:00.00','14:00:00'),
       (3,'TUE',1,'09:00:00.00','15:00:00'),
       (4,'WED',1,'10:00:00.00','17:00:00'),
       (5,'THU',1,'13:00:00.00','18:00:00')

DECLARE @D1 DATE, @D2 DATE
SET @D1 = '2017-02-19'
SET @D2 = '2017-02-28'

;WITH Calendar AS
(
    SELECT @D1 AS [DateVal], LEFT(DATENAME(WEEKDAY,@D1),3) AS [DWName]
    UNION ALL
    SELECT DATEADD(DAY,1,DateVal), LEFT(DATENAME(WEEKDAY,DATEADD(DAY,1,DateVal)),3)
    FROM Calendar
    WHERE DateVal<@D2
)


SELECT S.DoctorId, C.DateVal, S.[Day], S.FromTime, S.ToTime
FROM @Shift S
JOIN Calendar C ON S.[Day]=C.DWName