sql - 确定连续和独立的 PTO 天数
问题描述
根据反馈,我正在重组我的问题。
我正在 Presto 数据库上使用 SQL。
我的目标是报告自 2018 年初以来连续几天使用 PTO 或病假的员工。我想要的输出将包含员工在开始和结束日期所花费的各个时间岛,大致如下:
我使用的主表是 d_employee_time_off
只有两个 time_off_type_name:PTO 和 Sick Leave。
ds 是一个日期戳,我使用最新的 ds(通常是当前日期)
我可以访问名为 d_date 的日期表
我可以在 d_employee_time_off.time_off_date = d_date.full_date 加入表格
我希望我以一种可以理解的方式来组织这个问题。
解决方案
我相信这里需要将休息日材料加入日历表。
在下面的示例解决方案中,我正在“即时”生成这个,但我认为您确实有自己的解决方案。同样在我的示例中,我使用了字符串“Monday”并从该字符串向后移动(或者,您可以使用“Friday”并向前移动)。我不热衷于依赖语言的解决方案,但由于我不是 Presto 用户,因此无法在 Presto 上测试任何东西。因此,下面的示例使用了您自己的一些逻辑,但使用我相信您可以转换为 Presto 的 SQL Server 语法:
询问:
;WITH
Digits AS (
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
)
, cal AS (
SELECT
ca.number
, dateadd(day,ca.number,'20180101') as cal_date
, datename(weekday,dateadd(day,ca.number,'20180101')) weekday
FROM Digits [1s]
CROSS JOIN Digits [10s]
CROSS JOIN Digits [100s] /* add more like this as needed */
cross apply (
SELECT
[1s].digit
+ [10s].digit * 10
+ [100s].digit * 100 /* add more like this as needed */
AS number
) ca
)
, time_off AS (
select
*
from cal
inner join mytable t on (cal.cal_date = t.time_off_date and cal.weekday <> 'Monday')
or (cal.cal_date between dateadd(day,-2,t.time_off_date)
and t.time_off_date and datename(weekday,t.time_off_date) = 'Monday')
)
, starting_points AS (
SELECT
employee_id,
cal_date,
dense_rank() OVER(partition by employee_id
ORDER BY
time_off_date
) AS rownum
FROM
time_off A
WHERE
NOT EXISTS (
SELECT
*
FROM
time_off B
WHERE
B.employee_id = A.employee_id
AND B.cal_date = DATEADD(day, -1, A.cal_date)
)
)
, ending_points AS (
SELECT
employee_id,
cal_date,
dense_rank() OVER(partition by employee_id
ORDER BY
time_off_date
) AS rownum
FROM
time_off A
WHERE
NOT EXISTS (
SELECT
*
FROM
time_off B
WHERE
B.employee_id = A.employee_id
AND B.cal_date = DATEADD(day, 1, A.cal_date)
)
)
SELECT
S.employee_id,
S.cal_date AS start_range,
E.cal_date AS end_range
FROM
starting_points S
JOIN
ending_points E
ON E.employee_id = S.employee_id
AND E.rownum = S.rownum
order by employee_id
, start_range
结果:
employee_id start_range end_range
1 200035 02.01.2018 02.01.2018
2 200035 20.04.2018 27.04.2018
3 200037 27.01.2018 29.01.2018
4 200037 31.03.2018 02.04.2018
见:http ://rextester.com/MISZ50793
CREATE TABLE mytable(
ID INT NOT NULL
,employee_id INTEGER NOT NULL
,type VARCHAR(3) NOT NULL
,time_off_date DATE NOT NULL
,time_off_in_days INT NOT NULL
);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (1,200035,'PTO','2018-01-02',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (2,200035,'PTO','2018-04-20',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (3,200035,'PTO','2018-04-23',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (4,200035,'PTO','2018-04-24',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (5,200035,'PTO','2018-04-25',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (6,200035,'PTO','2018-04-26',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (7,200035,'PTO','2018-04-27',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (8,200037,'PTO','2018-01-29',1);
INSERT INTO mytable(id,employee_id,type,time_off_date,time_off_in_days) VALUES (9,200037,'PTO','2018-04-02',1);
推荐阅读
- amazon-web-services - aws SSM 会话仅读取应用程序日志
- angular - NGRX 从存储的数组中删除元素
- android - 是否可以从 android 智能手机收集 3 天或更长时间的 IMU 传感器数据?
- qt - 如何在 TCP / IP 模式下创建 WITS 连接?
- rest - 在 OneDrive 中查找子文件夹的问题
- java - Spring Data MongoDB - 向标准中的同一字段添加多个键
- c# - asp.net 中的粘性标题
- h2o - XGBOOST scale_pos_weight
- azure-data-factory-2 - ADFv2 检查 API 中是否存在文件(HTTP 链接服务)
- django - 如何将 Django URL 参数限制为选项列表?