Find employee shift by date name between date range

Question

I have three table which are following,

Table HrShift :

Id  ShiftName       DaysOfWeek                                          StartTime   EndTime     StartDate   EndDate
1   Day Shift-1     Sunday,Monday,Tuesday,Wednesday,Thursday,Friday     09:00 AM    06:00 PM    2016-01-01  NULL
2   Day Shift-2     Sunday,Monday,Tuesday,Wednesday,Thursday,Friday     10:00 AM    07:00 PM    2010-01-01  NULL
3   Day Shift-3     Sunday,Monday,Tuesday,Wednesday,Thursday,Friday     11:00 AM    08:00 PM    2010-01-01  NULL

Table ShiftType :

Id  Name
1   Primary
2   Roster

Table EmployeeShifts :

Id  EmpId   HrShiftId   ShiftTypeId     StartDate   EndDate 
1   1       1           1               2018-01-01  NULL    
2   1       2           2               2018-02-01  2018-02-01
3   2       1           1               2018-01-01  NULL
4   2       2           2               2018-02-01  2018-02-01
5   2       3           2               2018-02-01  2018-02-01

Create table commands :

CREATE TABLE [HrShifts](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ShiftName] [nvarchar](max) NULL,
    [DaysOfWeek] [nvarchar](max) NULL,
    [StartTime] [nvarchar](max) NULL,
    [EndTime] [nvarchar](max) NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL);

CREATE TABLE [ShiftType](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL);

CREATE TABLE [EmployeeShifts](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [HrShiftId] [int] NOT NULL,
    [ShiftTypeId] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL);

I need to find single assigned shift for each employee between two dates. ShiftType Roster will get the priority over Primary. Need to check DaysOfWeek column in HrShift table which have to match by the day name for the date.

Expected Outcome between (2018-01-31 and 2018-2-2) is following

Date        EmpId   ShiftType   HrShiftId   ShiftName       StartTime   EndTime
2018-01-31  1       1           1           Day Shift-1     09:00 AM    06:00 PM
2018-02-01  1       2           2           Day Shift-2     10:00 AM    07:00 PM
2018-02-02  1       1           1           Day Shift-1     09:00 AM    06:00 PM
2018-01-31  2       1           1           Day Shift-1     09:00 AM    06:00 PM
2018-02-01  2       2           2           Day Shift-2     10:00 AM    07:00 PM
2018-02-02  2       2           3           Day Shift-3     11:00 AM    08:00 PM

Answer 1

First, you need to generate the days. For this you can use a recursive CTE (or calendar table) or similar method.

with days as (
      select convert(date, '2018-01-31') as dte
      union all
      select dateadd(day, 1, dte)
      from days
      where dte < '2018-02-02'
     )
select d.dte, es.*, hs.startTime, hs.endTime
from EmployeeShifts es join
     HrShift hs
     on es.HrShiftId = hs.id cross join
     days d
where d.dte >= hs.startDate and
      (d.dte < hs.endDate or hs.endDate is null) and
      hs.daysofweek like '%' + datename(weekday, d.dte) + '%';

I think the where clause covers all the matching logic that you want between the date and the schedules.

Answer 2

I have changed few data values to show you different results: change temp variable names with yours.

here I make list of dates first @S_Date and @E_Date are inputs:

    DECLARE @S_Date AS DATETIME
    DECLARE @E_Date AS DATETIME
    DECLARE @TotalDays AS INT
    DECLARE @DisplayDate AS VARCHAR(50)
    DECLARE @WDN AS INT
    DECLARE @WDName AS VARCHAR(20)

    SET @S_Date =CONVERT(datetime,'01/31/2019')
    SET @E_Date =CONVERT(datetime,'02/02/2019')
    SET @WDN = DATEPART(WEEKDAY,@S_Date)
    SET @WDName = DATENAME(weekday,@S_Date)
    SET @TotalDays = DATEDIFF(dd,@S_Date, DATEADD(dd,1,@E_Date))
    SET @DisplayDate =  DATENAME(weekday, @S_Date)+' ' + DATENAME(dd,@S_Date)+' '+ DATENAME(m, @S_Date)+' '+ DATENAME(yy, @S_Date)


    CREATE TABLE #Monthdaytable(
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [DisplayDate] [varchar](50) NULL,
        [Actualdate] [datetime] NULL,
        [Week_Day_Number] INT NULL,
        [Week_Day_Name] VARCHAR(20) NULL
    ) ON [PRIMARY]

    DECLARE @i AS INT
    SET @i = 1  
    WHILE(@i<=@TotalDays)
    BEGIN
        INSERT INTO #Monthdaytable
                ( DisplayDate, ActualDate ,Week_Day_Number, Week_Day_Name)
        VALUES  (@DisplayDate, @S_Date, @WDN, @WDName)
        SET @S_Date = DATEADD(dd,1,@S_Date)
        SET @WDN = DATEPART(WEEKDAY,@S_Date)
        SET @WDName = DATENAME(weekday,@S_Date)
        SET @DisplayDate =DATENAME(weekday, @S_Date)+' ' + DATENAME(dd,@S_Date)+' '+  DATENAME(m, @S_Date)+' '+ DATENAME(yy, @S_Date)
        SET @i = @i+1
    END

Just your tables:

    CREATE TABLE #HrShift (Id int, ShiftName varchar(50), DaysOfWeek varchar(500),StartTime time(7),EndTime time(7), StartDate datetime, EndDate datetime)

INSERT INTO #HrShift ( Id,  ShiftName,       DaysOfWeek, StartTime,   EndTime,     StartDate,   EndDate) VALUES (1,   'Day Shift-1',     'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday' ,    '09:00 AM' ,   '06:00 PM',    '2016-01-01' , NULL)
INSERT INTO #HrShift ( Id,  ShiftName,       DaysOfWeek, StartTime,   EndTime,     StartDate,   EndDate) VALUES (2,  'Day Shift-2' ,  'Sunday,Monday,Tuesday,Wednesday,Thursday'   ,  '10:00 AM'   , '07:00 PM' ,   '2010-01-01' , NULL)
INSERT INTO #HrShift ( Id,  ShiftName,       DaysOfWeek, StartTime,   EndTime,     StartDate,   EndDate) VALUES (3,   'Day Shift-3' ,  'Sunday,Monday,Tuesday,Wednesday,Thursday' ,    '11:00 AM'   , '08:00 PM' ,   '2010-01-01' , NULL)

    CREATE TABLE #ShiftType (Id int,  Name varchar(50))


INSERT INTO #ShiftType(Id,  Name) VALUES (1,   'Primary')
INSERT INTO #ShiftType(Id,  Name) VALUES (2,   'Roster')

CREATE TABLE #EmployeeShifts (Id int,  EmpId int,   HrShiftId int,  ShiftTypeId int,    StartDate datetime,   EndDate datetime)


INSERT INTO #EmployeeShifts(Id,  EmpId,   HrShiftId,   ShiftTypeId,     StartDate,   EndDate ) VALUES(1,   1,       1,           1,               '2018-01-01',  NULL    )
INSERT INTO #EmployeeShifts(Id,  EmpId,   HrShiftId,   ShiftTypeId,     StartDate,   EndDate ) VALUES(2,   1,       2,           2,               '2018-02-01',  '2018-02-01')
INSERT INTO #EmployeeShifts(Id,  EmpId,   HrShiftId,   ShiftTypeId,     StartDate,   EndDate ) VALUES(3,   2,       1,           1,               '2018-01-01',  NULL)
INSERT INTO #EmployeeShifts(Id,  EmpId,   HrShiftId,   ShiftTypeId,     StartDate,   EndDate ) VALUES(4,   2,       2,           2,               '2018-02-01',  '2019-02-05')
INSERT INTO #EmployeeShifts(Id,  EmpId,   HrShiftId,   ShiftTypeId,     StartDate,   EndDate ) VALUES(5,   2,       3,           2,               '2018-02-01',  '2019-02-01')


    SELECT * FROM #Monthdaytable 
    --SELECT * FROM #HrShift
    --SELECT * FROM #ShiftType
    --SELECT * FROM #EmployeeShifts

This is the query you need:

SELECT m.DisplayDate ,EmpId,ShiftName,t.Name,StartTime,EndTime
        FROM #EmployeeShifts e INNER JOIN
                #ShiftType t ON  e.ShiftTypeId = t.Id INNER JOIN
                #HrShift s ON s.Id = e.HrShiftId INNER JOIN
                #Monthdaytable m ON 1 = 1
        WHERE CONVERT(varchar,m.Actualdate,112)>=CONVERT(varchar,e.StartDate,112)
                and  CONVERT(varchar,m.Actualdate,112)<=CONVERT(varchar,ISNULL(e.EndDate, m.Actualdate),112)            
                and s.DaysOfWeek  LIKE '%'+m.Week_Day_Name+'%' 

to drop temp

    DROP TABLE #EmployeeShifts
    DROP TABLE #ShiftType
    DROP TABLE #HrShift
    DROP TABLE #Monthdaytable