时间戳

首页 > 解决方案 > 在不爆炸每个组合的情况下查找未覆盖的时期

sql - 在不爆炸每个组合的情况下查找未覆盖的时期

问题描述

我有以下两张表

人们

+--------+---------------+-------------+
|  Name  | ContractStart | ContractEnd |
+--------+---------------+-------------+
| Kate   |      20180101 |    20181231 |
| Sawyer |      20180101 |    20181231 |
| Ben    |      20170601 |    20181231 |
+--------+---------------+-------------+

转移

+---------+--------+------------+----------+
| Station |  Name  | ShiftStart | ShiftEnd |
+---------+--------+------------+----------+
| Swan    | Kate   |   20180101 | 20180131 |
| Arrow   | Kate   |   20180301 | 20180331 |
| Arrow   | Kate   |   20180401 | 20181231 |
| Flame   | Sawyer |   20180101 | 20181231 |
| Swan    | Ben    |   20180101 | 20181231 |
+---------+--------+------------+----------+

这意味着，例如，Kate 将从 20180101 到 20181231 可用。在此期间，她将在 Swan 站从 20180101 到 20180131 工作，在 Arrow 站从 20180301 到 20180331 和从 20180401 到 20181231 工作。

我的目标是来到下表

+------+---------------+-------------+
|      | VacationStart | VacationEnd |
+------+---------------+-------------+
| Kate |      20180201 |    20180228 |
| Ben  |      20170601 |    20171231 |
+------+---------------+-------------+

这意味着凯特将从 20180201 到 20180228 自由。

我的第一个想法是创建一个包含 2017 年和 2018 年的每一天的表格，比如说 a CalTable，然后加入表格People以查找每个人都应该有空的每一天。此时 JOIN 再次将结果表与Shifts有证据的天NOT BETWEEN ShiftStart AND ShiftEnd。考虑到我有将近 1.000.000 人，并且通常在10 到 20 年之间ContractStart，这些步骤给了我正确的结果，但速度很慢。ContractEnd

以更聪明和快速的方式获得结果的正确方法是什么？

谢谢。 这是 db<>Fiddle 上示例的数据

对于@A_Name_Does_Not_Matter，这是我的尝试

CREATE TABLE #CalTable([ID] VARCHAR(8) NOT NULL)

DECLARE @num int
SET @num = 20170101

WHILE (@num <= 20181231)
BEGIN
    INSERT INTO #CalTable([ID])
    SELECT @num AS [ID]

    SET @num = @num + 1
END

SELECT X.[Name], X.[TIMEID]
FROM (
    -- All day availables
    SELECT DISTINCT A.[Name],B.[ID] AS [TIMEID]
    FROM #People A INNER JOIN #CalTable B
    ON B.[ID] BETWEEN A.[ContractStart] AND A.[ContractEnd]
) X
LEFT JOIN (
    -- Working day
    SELECT DISTINCT A.[Name],B.[ID] AS [TIMEID]
    FROM #People A INNER JOIN #CalTable B
    ON B.[ID] BETWEEN A.[ContractStart] AND A.[ContractEnd]
    INNER JOIN #Shifts C ON A.[Name]=C.[Name] AND B.[ID] BETWEEN C.[ShiftStart] AND C.[ShiftEnd]
) Z
ON X.[Name]=Z.[Name] AND X.[TIMEID]=Z.[TIMEID]
WHERE Z.[Name] IS NULL
ORDER BY X.[Name],X.[TIMEID]

然后聚合这个查询的日期。

标签： sqlsql-serverdatetimesql-server-2005period

解决方案

因此，一个人的开始日期可能是假期的开始，您可以通过使用 CROSS APPLY 找到他们第一次轮班的日期（减去 1 天）来找到该假期的结束日期，以获取按日期排序的 TOP 1 班次

在他们没有轮班的不寻常情况下，他们的假期在合同结束日期结束。

未来的假期然后在轮班后的第二天开始，并在下一个轮班的前一天结束（可以通过 OUTER APPLY 找到），如果没有进一步的轮班，则默认为约定的结束日期

SELECT p.name, p.contractStart vacationstart, p.ContractEnd vacationend from people p WHERE not exists(select 1 from shifts s where p.name = s.name)
UNION
SELECT p2.name,
        p2.contractStart vacationstart, 
        dateadd(day,-1,DQ.ShiftStart) as vacationend 
            from PEOPLE P2
            CROSS APPLY 
                (SELECT TOP 1 s2.ShiftStart FROM shifts s2 WHERE p2.name = s2.name  order by sfiftstart) DQ
                WHERE DQ.ShiftStart > p2.contractstart

UNION
select P3.NAME,
       dateadd(day,1,s3.ShiftEnd) vacationstart,
       COALESCE(dateadd(day,-1,  DQ2.shiftStart),P3.ContractEnd)  --you might have to add handling yourself for  removing a case where they work on their contract end date
       FROM people p3 JOIN shifts s3 on p3.name = s3.name
       OUTER APPLY (SELECT TOP 1 s4.shiftStart 
                                    from shifts s4
                                        where s4.name = p3.name 
                                              and 
                                              s4.shiftstart > s3.shiftstart 
                                        order by s4.shiftstart) DQ2

没有测试数据我很难验证。对于员工来说，我追求的是。

合约开始，Shift1Start - 1

Shift1End + 1，Shift2Start - 1

Shift2End + 1，Shift3Start - 1

Shift3End + 1，ContractEnd

然后添加“无班次”的情况，最后班次可能是连续的，导致持续时间为零或更短的假期 - 您可以通过使查询成为子查询来过滤这些，并简单地过滤

推荐阅读