时间戳

首页 > 解决方案 > 如何从工作计划中减去非工作计划以在 SQL 中生成新表？

sql - 如何从工作计划中减去非工作计划以在 SQL 中生成新表？

问题描述

我有一个标准化的工作时间表，在某一天看起来像这样：

Working Shift   PayDay      StartTime           EndTime
True    Shift1  2020-09-14  2020-09-14 06:00    2020-09-14 14:00
True    Shift2  2020-09-14  2020-09-14 14:00    2020-09-14 23:00
True    Shift3  2020-09-14  2020-09-14 23:00    2020-09-15 06:00

然后我有另一个表与上述工作时间表有偏差，看起来像这样：

Working Shift   PayDay      StartTime           EndTime
False   Shift1  2020-09-14  2020-09-14 10:00    2020-09-14 12:30

从这些表中，我想生成一个表，只显示可用的工作时间。该表应如下所示：

Working Shift   PayDay      StartTime           EndTime
True    Shift1  2020-09-14  2020-09-14 06:00    2020-09-14 10:00
True    Shift1  2020-09-14  2020-09-14 12:30    2020-09-14 14:00
True    Shift2  2020-09-14  2020-09-14 14:00    2020-09-14 23:00
True    Shift3  2020-09-14  2020-09-14 23:00    2020-09-15 06:00

在这种情况下，因为我在第 1 班的中间有非工作时间，所以结果表将包含该班次的两个条目。

我是 SQL 新手，不知道如何解决这个问题，有人有什么建议吗？

标签： sqlsql-server

解决方案

您可以在fiddle中查看答案。为了将来使用，表创建查询可以是：

CREATE TABLE table1 ( 
  [Working] [nvarchar](25),
  [Shift] [nvarchar](25),
  [PayDay] [nvarchar](25),
  [StartTime] datetime,
  [EndTime] datetime)
  
CREATE TABLE table2 ( 
  [Working] [nvarchar](25),
  [Shift] [nvarchar](25),
  [PayDay] [nvarchar](25),
  [StartTime] datetime,
  [EndTime] datetime)

INSERT INTO table1 VALUES 
('True', 'Shift1', '2020-09-14', '2020-09-14 06:00', '2020-09-14 14:00'),
('True', 'Shift2', '2020-09-14', '2020-09-14 14:00', '2020-09-14 23:00')

INSERT INTO table2 VALUES 
('False', 'Shift1', '2020-09-14', '2020-09-14 10:00', '2020-09-14 12:30')

由于您需要一个比前一个具有更多行的新表，因此您可以按照评论中的说明解决问题UNION。之后，可以应用逻辑参数来创建您的视图。Final_EndTime是通过获取下一个StartTime并且仅用于使用的最后一个条目来创建的EndTime。WhileFinal_StartTime使用的是EndTimewhenWorking列False。

SELECT 'True' AS Final_Working, A.PayDay, A.Shift,
  CASE WHEN A.Working = 'False' THEN A.EndTime ELSE A.StartTime END AS Final_StartTime,
  CASE WHEN LEAD(A.Working) OVER (ORDER BY A.StartTime) <> '' THEN LEAD(A.StartTime) OVER (ORDER BY A.StartTime) ELSE A.EndTime END AS Final_EndTime
FROM (
SELECT t1.*
FROM table1 AS t1
UNION ALL 
SELECT t2.*
FROM table2 AS t2)A
ORDER BY A.StartTime

推荐阅读