在日期范围内按日期名称查找员工班次

Question

下面我有三个表，

表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

表ShiftType：

Id  Name
1   Primary
2   Roster

表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 [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);

我需要在两个日期之间为每个员工找到一个分配的班次。ShiftType花名册将获得高于主要球员的优先级。需要检查HrShift表中的DaysOfWeek列，该列必须与日期的日期名称相匹配。

(2018-01-31和2018-2-2)之间的预期结果如下

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

我更改了几个数据值，以便向您显示不同的结果:更改temp变量的名称。

这里我先做一个日期列表，@S_Date和@E_Date是输入：

    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

只有你的表：

    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

这是您需要的查询：

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+'%' 

降低温度的步骤

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

Answer 2

首先，您需要生成日期。为此，您可以使用递归CTE (或日程表)或类似方法。

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) + '%';

我认为where子句涵盖了日期和日程表之间需要的所有匹配逻辑。