仅跟踪工作日的连续缺勤天数 SQL [英] Tracking continuous days of absence from work days only SQL
问题描述
我正在尝试创建一个表，该表获取员工生病的日期，并创建一个新列以提供疾病 ID"，该列将标识多个日期的唯一缺勤实例.我已经设法做到了这一点，但是我现在需要考虑一个包含每个员工工作模式的表格，这会让我知道是否有人在一周中的某一天上班.
I'm trying to create a table which takes dates of when a employee is sick and create a new column to provide a "sickness ID", which will identify a unique instance of absence over several dates. I've managed to do this, however I now need to factor in a table which contains the working pattern of each employee, which will let me know if someone was due in work on a given day of the week.
这可以使用两个表中的 day_no 列以及 employee_number 进行连接.
This can be joined using the day_no column in both tables along with the employee_number.
我发布了一个这个问题早些时候，@GMB 有一个很好的解决方案，但是我需要增加工作时间.
I posted a this question earlier and had a great solution by @GMB, however I need this addition of the working hours.
我有一个名为 sickness 的表格，看起来像这样
I have table called sickness which looks like this
date_sick day_no day_name employee_number hours_lost working_hours
2020-07-14 2 Tuesday 001 7.5 7.5
2020-07-15 3 Wednesday 001 7.5 7.5
2020-07-16 4 Thursday 001 7.5 7.5
2020-07-17 5 Friday 001 7.5 7.5
2020-07-21 2 Tuesday 001 7.5 7.5
2020-07-22 3 Wednesday 001 7.5 7.5
2020-07-23 4 Thursday 001 7.5 7.5
2020-07-24 5 Friday 001 7.5 7.5
2020-07-28 2 Tuesday 001 7.5 7.5
2020-07-29 3 Wednesday 001 7.5 7.5
2020-07-30 4 Thursday 001 7.5 7.5
2020-07-31 5 Friday 001 7.5 7.5
2020-09-09 3 Wednesday 001 7.5 7.5
2020-09-10 4 Thursday 001 7.5 7.5
2020-07-22 3 Wednesday 002 8 8
2020-07-23 4 Thursday 002 8 8
我的工作时间表是这样的:
And my working hours table looks like this:
employee_number day_no working_hours
001 1 0
001 2 7.5
001 3 7.5
001 4 7.5
001 5 7.5
001 6 0
001 7 0
002 1 8
002 2 8
002 3 8
002 4 8
002 5 8
002 6 0
002 7 0
使用以下语句，我可以应用唯一的疾病 ID，该 ID 标识员工连续缺勤的唯一实例，该实例对于员工和缺勤日期都是唯一的，由下式给出:
Using the following statement, I'm able to apply a unique sickness ID which identifies a unique instance of employee absence over consecutive dates, which is unique to both the employee and the dates there were absence, given by:
IF OBJECT_ID('dbo.sickness ', 'u') IS NOT NULL DROP TABLE dbo.sickness
CREATE TABLE dbo.sickness (date_sick date
, day_no int
, day_name varchar(10)
, employee_number char(5)
, hours_lost float
, working_hours float)
INSERT INTO dbo.sickness (date_sick, day_no, day_name, Employee_Number, Hours_Lost, Working_Hours)
VALUES
('2020-07-14', '2', 'Tuesday', '001', '7.5', '7.5'),
('2020-07-15', '3', 'Wednesday', '001', '7.5', '7.5'),
('2020-07-16', '4', 'Thursday', '001', '7.5', '7.5'),
('2020-07-17', '5', 'Friday', '001', '7.5', '7.5'),
('2020-07-21', '2', 'Tuesday', '001', '7.5', '7.5'),
('2020-07-22', '3', 'Wednesday', '001', '7.5', '7.5'),
('2020-07-23', '4', 'Thursday', '001', '7.5', '7.5'),
('2020-07-24', '5', 'Friday', '001', '7.5', '7.5'),
('2020-07-28', '2', 'Tuesday', '001', '7.5', '7.5'),
('2020-07-29', '3', 'Wednesday', '001', '7.5', '7.5'),
('2020-07-30', '4', 'Thursday', '001', '7.5', '7.5'),
('2020-07-31', '5', 'Friday', '001', '7.5', '7.5'),
('2020-09-09', '3', 'Wednesday', '001', '7.5', '7.5'),
('2020-09-10', '4', 'Thursday', '001', '7.5', '7.5'),
('2020-07-22', '3', 'Wednesday', '002', '8', '8'),
('2020-07-23', '4', 'Thursday', '002', '8', '8')
GO
IF OBJECT_ID('dbo.working_hours ', 'u') IS NOT NULL DROP TABLE dbo.working_hours
CREATE TABLE dbo.working_hours (employee_number char(5)
, day_no int
, working_hours float)
INSERT INTO dbo.working_hours (employee_number, day_no, working_hours)
VALUES
('001', '1', '0'),
('001', '2', '7.5'),
('001', '3', '7.5'),
('001', '4', '7.5'),
('001', '5', '7.5'),
('001', '6', '0'),
('001', '7', '0'),
('002', '1', '8'),
('002', '2', '8'),
('002', '3', '8'),
('002', '4', '8'),
('002', '5', '8'),
('002', '6', '0'),
('002', '7', '0');
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY employee_number ORDER BY date_sick) AS rn
FROM dbo.sickness s)
SELECT c.date_sick,
c.day_no,
c.day_name,
c.employee_number,
c.hours_lost,
w.working_hours,
DENSE_RANK() OVER (ORDER BY C.employee_number, DATEADD(DAY, -C.rn, C.date_sick)) AS sickness_id
FROM CTE C
JOIN working_hours w
ON c.employee_number = w.employee_number
AND c.day_no = w.day_no
ORDER BY C.employee_number,
C.date_sick
DROP TABLE dbo.sickness
DROP TABLE dbo.working_hours
这将输出下表:
date_sick day_no day_name employee_number hours_lost working_hours sickness_id
2020-07-14 2 Tuesday 001 7.5 7.5 1
2020-07-15 3 Wednesday 001 7.5 7.5 1
2020-07-16 4 Thursday 001 7.5 7.5 1
2020-07-17 5 Friday 001 7.5 7.5 1
2020-07-21 2 Tuesday 001 7.5 7.5 2
2020-07-22 3 Wednesday 001 7.5 7.5 2
2020-07-23 4 Thursday 001 7.5 7.5 2
2020-07-24 5 Friday 001 7.5 7.5 2
2020-07-28 2 Tuesday 001 7.5 7.5 3
2020-07-29 3 Wednesday 001 7.5 7.5 3
2020-07-30 4 Thursday 001 7.5 7.5 3
2020-07-31 5 Friday 001 7.5 7.5 3
2020-09-09 3 Wednesday 001 7.5 7.5 4
2020-09-10 4 Thursday 001 7.5 7.5 4
2020-07-22 3 Wednesday 002 8 8 5
2020-07-23 4 Thursday 002 8 8 5
这个问题是它对连续的几天进行分组，但只对同一周内的几天进行分组.前 12 行应该都具有相同的疾病 ID.我想要的是下表:
The issue with this is that it's grouping the consecutive days but only ones that are within the same week. The first 12 rows should all have the same sickness ID. What I want is the following table:
date_sick day_no day_name employee_number hours_lost working_hours sickness_id
2020-07-14 2 Tuesday 001 7.5 7.5 1
2020-07-15 3 Wednesday 001 7.5 7.5 1
2020-07-16 4 Thursday 001 7.5 7.5 1
2020-07-17 5 Friday 001 7.5 7.5 1
2020-07-21 2 Tuesday 001 7.5 7.5 1
2020-07-22 3 Wednesday 001 7.5 7.5 1
2020-07-23 4 Thursday 001 7.5 7.5 1
2020-07-24 5 Friday 001 7.5 7.5 1
2020-07-28 2 Tuesday 001 7.5 7.5 1
2020-07-29 3 Wednesday 001 7.5 7.5 1
2020-07-30 4 Thursday 001 7.5 7.5 1
2020-07-31 5 Friday 001 7.5 7.5 1
2020-09-09 3 Wednesday 001 7.5 7.5 2
2020-09-10 4 Thursday 001 7.5 7.5 2
2020-07-22 3 Wednesday 002 8 8 3
2020-07-23 4 Thursday 002 8 8 3
有什么想法吗?也许将它连接到日历表?
Any ideas? Maybe connecting it to a calendar table?
推荐答案
我认为使用 lag() 来查看病假天数是否连续，然后累积总和是一种更好的分配方法疾病编号.
I think that using lag() to see if the sickness days are consecutive and then a cumulative sum is a better approach for assigning the sickness id.
我有点不清楚你到底想要什么.但这是一种方法:
I am a little unclear on what you want exactly. But here is one approach:
select date_sick, employee_number,
sum(case when working_hours > 0 and prev_working_hours > 0 and
dateadd(day, -1, date_sick) = prev_date_sick
then 0 else 1
end) over (partition by employee_number order by date_sick) as sickness_id
from (select s.*,
lag(date_sick) over (partition by employee_number order by date_sick) as prev_date_sick,
lag(working_hours) over (partition by employee_number order by date_sick) as prev_working_hours
from sickness s left join
working_hours wh
on s.date_sick = wh.working_hours
) s
order by employee_number, date_sick
这篇关于仅跟踪工作日的连续缺勤天数 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
