sql - 移位明智的拳头在sql中的特定值上得到错误的输出
问题描述
基于以下班次需要预期的输出。帮我解决这个问题。
员工冲压原始数据存储在下表中
事件：
Emp_Reader_Id DT Belongs_to
91 2019-12-15 08:08:03.000 2019-12-15
91 2019-12-14 20:02:24.000 2019-12-14
91 2019-12-14 08:03:55.000 2019-12-14
91 2019-12-14 00:01:31.000 2019-12-14
91 2019-12-13 16:10:08.000 2019-12-13
91 2019-12-12 20:06:37.000 2019-12-12
91 2019-12-12 08:05:42.000 2019-12-12
91 2019-12-11 20:07:37.000 2019-12-11
91 2019-12-10 20:11:02.000 2019-12-10
91 2019-12-10 08:03:05.000 2019-12-10
91 2019-12-09 20:08:03.000 2019-12-09
91 2019-12-09 08:06:18.000 2019-12-09
91 2019-12-08 20:01:58.000 2019-12-08
91 2019-12-08 08:02:34.000 2019-12-08
91 2019-12-08 00:07:02.000 2019-12-08
91 2019-12-07 16:01:42.000 2019-12-07
91 2019-12-07 08:04:41.000 2019-12-07
91 2019-12-06 20:14:08.000 2019-12-06
91 2019-12-06 08:06:33.000 2019-12-06
91 2019-12-05 20:01:06.000 2019-12-05
91 2019-12-05 09:06:46.000 2019-12-05
91 2019-12-04 20:00:23.000 2019-12-04
91 2019-12-04 11:19:23.000 2019-12-04
91 2019-12-03 20:03:01.000 2019-12-03
91 2019-12-03 08:05:59.000 2019-12-03
91 2019-12-02 20:02:21.000 2019-12-02
91 2019-12-02 09:09:35.000 2019-12-02
91 2019-12-01 19:56:47.000 2019-12-01
91 2019-12-01 08:08:11.000 2019-12-01
员工轮班计划在一个月内按天安排，如下所示
班次时间表
emp_reader_id month_no year_no 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
91 12 2019 14 14 14 14 14 14 18 13 13 13 13 13 13 18 14
转移
shift_id shift_title
14 Night
18 Regular
班次详情如下
Shift_days
shift_id day_id day_start_time day_end_time night_shift isOffday
18 1 0.00 0.00 0 1
18 2 0.00 0.00 0 1
18 3 0.00 0.00 0 1
18 4 0.00 0.00 0 1
18 5 0.00 0.00 0 1
18 6 0.00 0.00 0 1
18 7 0.00 0.00 0 1
14 1 20.00 4.00 1 0
14 2 20.00 4.00 1 0
14 3 20.00 4.00 1 0
14 4 20.00 4.00 1 0
14 5 20.00 4.00 1 0
14 6 20.00 4.00 1 0
14 7 20.00 4.00 1 0
预期输出：
emp_reader_id in_time out_time
91 2019-12-14 08:03:51.000 2019-12-14 20:02:24.000
91 2019-12-13 16:10:08.000 2019-12-14 00:01:31.000
91 2019-12-12 08:05:42.000 2019-12-12 20:06:37.000
91 2019-12-11 20:07:37.000 2019-12-11 20:07:37.000
91 2019-12-10 08:03:05.000 2019-12-10 20:11:02.000
91 2019-12-09 08:06:18.000 2019-12-09 20:08:03.000
91 2019-12-08 08:02:02.000 2019-12-08 20:01:58.000
91 2019-12-07 16:01:42.000 2019-12-08 00:07:02.000
91 2019-12-06 20:14:08.000 2019-12-07 08:04:42.000
91 2019-12-05 20:01:06.000 2019-12-06 08:06:33.000
91 2019-12-04 20:00:23.000 2019-12-05 09:06:46.000
91 2019-12-03 20:03:01.000 2019-12-04 11:19:23.000
91 2019-12-02 20:02:21.000 2019-12-03 08:05:59.000
91 2019-12-01 19:56:47.000 2019-12-02 09:09:35.000
我试图得到上述结果，但在休假日它不合适
DECLARE @TempTable as Table
(emp_reader_id nvarchar(50),DT datetime,ExitDateTime datetime,Belongs_to date,seconds bigint )
;
WITH Level1
AS (
SELECT A.emp_reader_id,
CASE WHEN A.EventCatId=1 and (A.DT between dateadd(minute,-0, B.break_start_time) and B.break_end_time)
THEN B.break_start_time
WHEN A.EventCatId=0 and (A.DT between B.break_start_time and B.break_end_time)
THEN B.break_end_time
ELSE A.DT END AS
DT
,A.EventCatId
,A.Belongs_to
,ROW_NUMBER() OVER ( PARTITION BY A.Belongs_to,A.emp_reader_id ORDER BY DT ) AS RowNum
FROM dbo.trnevents A
left JOIN @TempProcessTable B on A.emp_reader_id = B.emp_reader_id and A.Belongs_to=B.att_date
where A.Belongs_to between @start_date and @end_date and B.emp_reader_id in (select emp_reader_id from employee where company_id=@company_id)
)
,
LEVEL2
AS (-- find the last and next event type for each row
SELECT A.emp_reader_id,A.DT , A.EventCatId ,COALESCE(LastVal.EventCatId, 10) AS LastEvent,
COALESCE(NextVal.EventCatId, 10) AS NextEvent ,A.Belongs_to
FROM Level1 A
LEFT JOIN Level1 LastVal
ON A.emp_reader_id = LastVal.emp_reader_id and A.Belongs_to=LastVal.Belongs_to
AND A.RowNum - 1 = LastVal.RowNum
LEFT JOIN Level1 NextVal
ON A.emp_reader_id = NextVal.emp_reader_id and A.Belongs_to=NextVal.Belongs_to
AND A.RowNum + 1 = NextVal.RowNum
)
,
Level3
AS (-- reapply row numbers to row-eliminated set
SELECT emp_reader_id ,DT , EventCatId ,LastEvent ,NextEvent ,ROW_NUMBER() OVER ( PARTITION BY emp_reader_id
ORDER BY DT ) AS RowNBr
,Belongs_to
FROM Level2
WHERE NOT ( EventCatId = 0
AND LastEvent = 0
)
AND NOT ( EventCatId = 1
AND NextEvent = 1
)
AND NOT ( EventCatId = 0
AND NextEvent = 10
)
AND NOT ( EventCatId = 1
AND LastEvent = 10
)
)
,
Level4
AS (-- pair enter and exit rows.
SELECT A.emp_reader_id , A.DT ,B.DT AS ExitDateTime ,A.Belongs_to
FROM Level3 A
INNER JOIN Level3 B ON A.emp_reader_id = B.emp_reader_id and A.Belongs_to=B.Belongs_to
AND A.RowNBr + 1 = B.RowNBr
and A.EventCatId = 0
AND B.EventCatId = 1
)
解决方案
您可以尝试从不同的角度处理事件（trnevents）的分类。
Unpivot Shift_schedule，你会得到一个特定读者每月的一组天数（称为 ReaderShiftDays）。反过来，加入 ReaderShiftDays 和 Shift_Days，结果将是每个 ReaderShiftDay 的定义/属性（白天、夜晚、离开）。最后，（左？）加入 Trnevents（在 emp_reader_id & ReaderShiftDays.Day = day(Trnevents.DT) 上，您会为每个 trnevent 获得班次的实际属性（将 trnevent 放在计划/班次的槽中）。知道每个 trnevent 的属性，您可以将其标记为属于以前的或计算调整后的开始日期/所属日期（如果有）。将所有这些按阅读器、班次日期和标志/开始日期/所属日期分组，您将获得 min&max (start&end) DT (trnevent)
CREATE TABLE TrnEvents
(
Emp_Reader_Id INT,
DT DATETIME,
BelongsTo DATE
);
INSERT INTO TrnEvents(Emp_Reader_Id, DT, BelongsTo)
VALUES
(91,'2019-12-15 08:08:03.000','2019-12-15'),
(91,'2019-12-14 20:02:24.000','2019-12-14'),
(91,'2019-12-14 08:03:55.000','2019-12-14'),
(91,'2019-12-14 00:01:31.000','2019-12-14'),
(91,'2019-12-13 16:10:08.000','2019-12-13'),
(91,'2019-12-12 20:06:37.000','2019-12-12'),
(91,'2019-12-12 08:05:42.000','2019-12-12'),
(91,'2019-12-11 20:07:37.000','2019-12-11'),
(91,'2019-12-10 20:11:02.000','2019-12-10'),
(91,'2019-12-10 08:03:05.000','2019-12-10'),
(91,'2019-12-09 20:08:03.000','2019-12-09'),
(91,'2019-12-09 08:06:18.000','2019-12-09'),
(91,'2019-12-08 20:01:58.000','2019-12-08'),
(91,'2019-12-08 08:02:34.000','2019-12-08'),
(91,'2019-12-08 00:07:02.000','2019-12-08'),
(91,'2019-12-07 16:01:42.000','2019-12-07'),
(91,'2019-12-07 08:04:41.000','2019-12-07'),
(91,'2019-12-06 20:14:08.000','2019-12-06'),
(91,'2019-12-06 08:06:33.000','2019-12-06'),
(91,'2019-12-05 20:01:06.000','2019-12-05'),
(91,'2019-12-05 09:06:46.000','2019-12-05'),
(91,'2019-12-04 20:00:23.000','2019-12-04'),
(91,'2019-12-04 11:19:23.000','2019-12-04'),
(91,'2019-12-03 20:03:01.000','2019-12-03'),
(91,'2019-12-03 08:05:59.000','2019-12-03'),
(91,'2019-12-02 20:02:21.000','2019-12-02'),
(91,'2019-12-02 09:09:35.000','2019-12-02'),
(91,'2019-12-01 19:56:47.000','2019-12-01'),
(91,'2019-12-01 08:08:11.000','2019-12-01');
GO
CREATE TABLE Shift_schedule
(
emp_reader_id int,
month_no smallint,
year_no smallint,
[1] tinyint,
[2] tinyint,
[3] tinyint,
[4] tinyint,
[5] tinyint,
[6] tinyint,
[7] tinyint,
[8] tinyint,
[9] tinyint,
[10] tinyint,
[11] tinyint,
[12] tinyint,
[13] tinyint,
[14] tinyint,
[15] tinyint
)
INSERT INTO Shift_schedule
(
emp_reader_id, month_no, year_no, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]
)
VALUES (91, 12, 2019, 14, 14, 14, 14, 14, 14, 18, 13, 13, 13, 13, 13, 13, 18, 14);
GO
CREATE TABLE Shift_days
(
shift_id int,
day_id tinyint,
day_start_time time,
day_end_time time,
night_shift bit,
isOffday bit
)
INSERT INTO Shift_days
VALUES
(18,1,'00:00','00:00',0,1),
(18,2,'00:00','00:00',0,1),
(18,3,'00:00','00:00',0,1),
(18,4,'00:00','00:00',0,1),
(18,5,'00:00','00:00',0,1),
(18,6,'00:00','00:00',0,1),
(18,7,'00:00','00:00',0,1),
(14,1,'20:00','04:00',1,0),
(14,2,'20:00','04:00',1,0),
(14,3,'20:00','04:00',1,0),
(14,4,'20:00','04:00',1,0),
(14,5,'20:00','04:00',1,0),
(14,6,'20:00','04:00',1,0),
(14,7,'20:00','04:00',1,0);
GO
SELECT *,
MIN(DT) OVER(PARTITION BY emp_reader_id, month_no, year_no, AdjustedBelongsTo) AS FromDateTime,
MAX(DT) OVER(PARTITION BY emp_reader_id, month_no, year_no, AdjustedBelongsTo) AS ToDateTime
--MIN(DT) AS FromDateTIme, MAX(DT) AS ToDateTime
FROM
(
SELECT *,
CASE
--better adjust the slot/shiftday instead of belongsto
--add logic of handling event.DTs here
WHEN (night_shift = 1 AND (DT = MinDT OR MinDT = MaxDT)) THEN DATEADD(day, -1, BelongsTo)
WHEN (night_shift = 0 AND( DT = MaxDT OR MinDT = MaxDT)) THEN DATEADD(day, 1, BelongsTo)
ELSE BelongsTo END AS AdjustedBelongsTo
FROM
(
SELECT
rsd.emp_reader_id, rsd.month_no, rsd.year_no, rsd.ShiftId, rsd.ShiftDay,
--shift attribs
sd.night_shift, sd.isOffday, sd.day_start_time, sd.day_end_time,
--event
e.DT, e.BelongsTo,
--ordinal or min/max (what about 3 or more events per day?)
--ROW_NUMBER() OVER(PARTITION BY e.BelongsTo ORDER BY DT) AS DayEventOrdinal,
MIN(e.DT) OVER(PARTITION BY rsd.emp_reader_id, rsd.month_no, rsd.year_no, rsd.ShiftId, rsd.ShiftDay, e.BelongsTo) AS MinDT,
MAX(e.DT) OVER(PARTITION BY rsd.emp_reader_id, rsd.month_no, rsd.year_no, rsd.ShiftId, rsd.ShiftDay, e.BelongsTo) AS MaxDT
--,*
FROM
(
SELECT *
FROM Shift_schedule
UNPIVOT
(
ShiftId for ShiftDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])
) AS unp
) AS rsd --ReaderShiftDays
LEFT JOIN Shift_days AS sd ON rsd.ShiftId = sd.shift_id AND DATEPART(weekday, DATEFROMPARTS(rsd.year_no, rsd.month_no, rsd.ShiftDay)) = sd.day_id
LEFT JOIN TrnEvents AS e ON rsd.emp_reader_id = e.Emp_Reader_Id AND DATEFROMPARTS(rsd.year_no , rsd.month_no, rsd.ShiftDay) = CAST(e.DT AS DATE)
) AS prep
) AS adjusted
--GROUP BY emp_reader_id, month_no, year_no, AdjustedBelongsTo
ORDER BY emp_reader_id, month_no, year_no, AdjustedBelongsTo;
推荐阅读
- python - 如何在Python中对包括整数的字符串列表进行排序
- angular - Firebase Firestore 如何查询子集合
- android - android RecyclerView 崩溃
- wordpress - woocommerce 产品变体，将下拉列表移至变体名称下方
- javascript - 使用 React JS 在所有段落中添加 SPAN 元素
- vue.js - Vuepress 链接和脚本标签在开发模式下不起作用
- python - 使用 SQL Server 2017 机器学习服务使用 Python 构建预测模型
- javascript - 如何使用将 Observable 映射到值是 observable 的对象的函数来映射 Observable？
- ssl - 在 Go 中手动验证 PEM 证书？
- html - HTML 内容可编辑，带有展开和居中的文本