Linked Questions

Popular Questions

Find employee shift by date name between date range

Asked by At

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

Related Questions