基于每个雇员在给定日期范围内表1行的轮换

rta7y2nd  于 2021-07-29  发布在  Java
关注(0)|答案(2)|浏览(361)

我需要一个小的帮助来理解从一个表(规则设置)到结果的循环行。
需要记住的几点:
现在我只有2个employee 4536的设置值,也可以是4或5。。
settingid每7天轮换一次,因为在规则设置表中我提到了雇员4536的weekofffrequency=7
它应该在设置ID 1和2之间重复,而不是99
它应该适用于所有员工
sql fiddler链接:http://sqlfiddle.com/#!18/9eecb/92011号
我已附上截图,以便我可以解释我的要求更清楚。
注:
规则设置表中的记录/行不固定。。。员工可以有n个设置。。。
但是rulesetting.weekofffrequency值对于雇员总是相同的

请问我是否有任何疑问。。。

enxuqcxy

enxuqcxy1#

我稍微修改了您的查询,并添加了rulesettingsmodified和newrotatesettingid。请检查一下这对你是否合适。

RuleSettingsModified AS
(
    SELECT Row_number() OVER (PARTITION BY rs.EmployeeID ORDER BY (SELECT NULL)) rn, rs.EmployeeID, rs.WeekOffFrequency
    FROM @RuleSettings rs   
)
(Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID))%(SELECT count(1) FROM RuleSettingsModified rsm WHERE rsm.EmployeeID = ed.EmployeeID)+1 AS NewRotateSettingId
;with dates_CTE (RosterDate) as (
    select @sdate 
    Union ALL
    select DATEADD(day, 1, RosterDate)
    from dates_CTE
    where RosterDate < @edate
),
EmployeeDates as (
SELECT  
ROW_NUMBER() over (partition by EmployeeId order by RosterDate) - 1 as Id,
EmployeeID,
RosterDate
FROM dates_CTE
CROSS APPLY (SELECT DISTINCT EmployeeId from  @RuleSettings)as cs
),
RuleSettingsModified AS
(
    SELECT Row_number() OVER (PARTITION BY rs.EmployeeID ORDER BY (SELECT NULL)) rn, rs.EmployeeID, rs.WeekOffFrequency
    FROM @RuleSettings rs   
)
,
EmployeeWeekOffFrequency as (

    SELECT *, 
    Id / (SELECT DISTINCT WeekOffFrequency from @RuleSettings where EmployeeID = ed.EmployeeID)+ 1  as WeekOffFrequency,
    (
        CASE 
        WHEN 
            ( 
                Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID)
            ) 
            < 
            (SELECT count(1) FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID)
        THEN  
        (
            SELECT SettingId FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID 
            AND SettingId = ((Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings  
            WHERE EmployeeID = ed.EmployeeID )) + 1 )
        ) 
        ELSE 99
        END
    ) as RotateSettingId,
    (Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID))%(SELECT count(1) FROM RuleSettingsModified rsm WHERE rsm.EmployeeID = ed.EmployeeID)+1 AS NewRotateSettingId
    from EmployeeDates as ed 
)
SELECT * FROM EmployeeWeekOffFrequency

请在这里查看完整的代码。

zdwk9cvp

zdwk9cvp2#

我实现了如下。在这里
expandrulesettings(add):将rulesettings行展开为雇员的每个settingid的周数fffrequency。
numberofrotate(add):获取rotate的数量(每个员工的所有settingid的数量)。
employeedates(change):为每个员工分配轮换的id日期。
employeeweekofffrequency(change):在匹配employeeid和id时加入employeedates和expandrulesettings表。

DECLARE @sdate date = '2020-09-10', @edate date = '2020-09-28'

;with dates_CTE (RosterDate) as (
    select @sdate 
    Union ALL
    select DATEADD(day, 1, RosterDate)
    from dates_CTE
    where RosterDate < @edate
),

-- Expand rows of RuleSettings to number of WeekOffFrequency each SettingId of employee.
ExpandRuleSettingsWork as (
  SELECT 0 AS IdForSettingId, SettingId, EmployeeID, ShiftPattern, WeekOffFrequency FROM @RuleSettings
  UNION ALL
  SELECT IdForSettingId+1, SettingId, EmployeeID, ShiftPattern, WeekOffFrequency FROM ExpandRuleSettingsWork
  WHERE IdForSettingId+1 < WeekOffFrequency
),
ExpandRuleSettings as (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY SettingId,IdForSettingId)-1 AS Id,
    * FROM ExpandRuleSettingsWork
),
-- SELECT * FROM ExpandRuleSettings ORDER BY EmployeeID,SettingId,Id

-- Get number of rotate (number of all SettingId of each employee).
NumberOfRotate as (
  SELECT EmployeeID, COUNT(EmployeeID) AS Num FROM ExpandRuleSettings GROUP BY EmployeeID
),
-- SELECT * FROM NumberOfRotate

EmployeeDates as (
SELECT  
(ROW_NUMBER() over (partition by EmployeeId order by RosterDate) - 1) % Num as Id,
EmployeeID,
RosterDate
FROM dates_CTE
CROSS APPLY NumberOfRotate
)

--select * from EmployeeDates
--order by EmployeeID , RosterDate

,
EmployeeWeekOffFrequency as (
  SELECT ed.*, er.SettingId
  FROM EmployeeDates as ed INNER JOIN ExpandRuleSettings as er
  ON ed.EmployeeID=er.EmployeeID AND 
    ed.Id = er.Id
)

SELECT * FROM EmployeeWeekOffFrequency

相关问题