将重叠的时间段外推到vin SQL之前、重叠和之后

我的数据将在不同的行中包含重叠的日期。对于包含重叠的每两行，我需要将其适当地分解如下。

首先，数据如下：

EMPID FIRSTNAME SURNAME ACTIVITY  START_DATE          END_DATE            HOURS_PER_DAY STATION
101   ANDREW    SMITH   AVAILABLE 2013-07-08 09:00:00 2013-07-08 17:00:00 8.00             LONDON
101   ANDREW    SMITH   SICKNESS  2013-07-08 07:00:00 2013-07-08 12:00:00 5.00             LONDON

原因是数据最初来自两个独立的表，我已经将两个表的内容插入其中。

这些数据最终将按每人分组，提供"每天小时数"列的总数。在上表中，这个人将有13个小时，但正如我们在上面看到的，两个时段的时间跨度都只有10个小时。

在上面的例子中，我需要将结果分为3行——重叠前的位、重叠和重叠后的位：

EMPID  NAME ACTIVITY  START_DATE          END_DATE            HOURS_PER_DAY STATION
101   JOHN  SICKNESS  2013-07-08 07:00:00 2013-07-08 09:00:00 2             LONDON
101   JOHN  SICKNESS  2013-07-08 09:00:00 2013-07-08 12:00:00 3             LONDON
101   JOHN  AVAILABLE 2013-07-08 12:00:00 2013-07-08 17:00:00 5             LONDON

对于重叠的时间段，必须将其归类为疾病。请注意，除了可用和疾病之外，还有其他选项，但唯一会重叠的是疾病与任何其他类型的疾病，例如"疾病"one_answers"可用"之间的冲突，或"疾病"与"训练"之间的碰撞。

此外，数据已经被划分为24小时的时间段——也就是说，我已经将一个跨越4天的患病期外推到4行，这就是为什么每天的小时数列永远不会超过24小时——如果存在完整的24小时时间段，那么结束日期将恰好在开始日期后1天——开始日期和结束日期都将在午夜。当我尝试执行所需操作时，我尝试按EMPID、NAME、CAST（START_DATE）AS DATE、CAST）（END_DATE AS DATE）和STATION对数据进行分组。这些字段将确定需要比较的分组。

请注意，目前大约有2500行数据与所提供的数据相似，可能存在以下类型的重叠（如果AVAILABLE条目从09:00开始，到17:00结束-请注意，事实并非如此，我只是以其为例：

病态和可用在完全相同的时间开始和结束-例如，病态从09:00开始，到17:00结束（结果8小时病态）
病态在AVAILABLE条目之前开始，并在条目中间结束：例如，病态从07:00开始，到12:00结束（结果：2小时病态，重叠3小时病态，5小时AVAILABLE）
病态开始于AVAILABLE条目的中间，并在AVAILABLE条目之后结束，例如病态开始于12:00，结束于22:00（结果：3小时可用，5小时重叠病态，5小时可用）
生病从AVAILABLE条目开始，到AVAILABEL条目结束，例如，生病从11:00开始，到14:00结束（结果：2小时可用，3小时重叠生病，3小时可用）
疾病在AVAILABLE条目之前开始，在AVAIBLE条目之后结束，例如疾病从06:00开始，到18:00结束（结果：12小时患病）
疾病在AVAILABLE条目的中途开始，并在同一时间结束
疾病开始于AVAILABLE条目的中途，并在AVAILABEL条目期间结束

请再次注意，"AVAILABLE"并不是唯一的选项。

我的查询目前已经到了以本文顶部的格式返回数据的地步，该格式存储在一个临时表@INDIVIDUALDAYS中-我现在想做任何需要@INDIVID UALDAYS的事情来推断它，如上所述。

我希望数据以完全相同的格式返回，具有相同的列（只是外推到多行上，因为我需要对其进行进一步的操作和计算

如果需要，我很乐意提供我目前的代码，但它有200多行，我相信我已经给了你足够的信息

以下是一些涵盖上述7种不同渗透的样本数据：

101 Andrew  Smith   Available   2014-08-19 09:00:00.000 2014-08-19 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-19 09:00:00.000 2014-08-19 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-20 12:00:00.000 2014-08-20 19:00:00.000 7.00    London
101 Andrew  Smith   Available   2014-08-20 09:00:00.000 2014-08-20 17:00:00.000 8.00    London
101 Andrew  Smith   Available   2014-08-21 09:00:00.000 2014-08-21 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-21 04:00:00.000 2014-08-21 12:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-22 06:00:00.000 2014-08-22 18:00:00.000 12.00   London
101 Andrew  Smith   Available   2014-08-22 09:00:00.000 2014-08-22 17:00:00.000 8.00    London
101 Andrew  Smith   Available   2014-08-23 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-23 11:00:00.000 2014-08-23 14:00:00.000 3.00    London
101 Andrew  Smith   Available   2014-08-24 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-24 09:00:00.000 2014-08-23 14:00:00.000 3.00    London
101 Andrew  Smith   Available   2014-08-25 09:00:00.000 2014-08-23 17:00:00.000 8.00    London
101 Andrew  Smith   SICKNESS    2014-08-25 11:00:00.000 2014-08-23 17:00:00.000 3.00    London

要做到这一点，首先需要将两行组合为一行。一旦将所有数据作为一行，就可以执行一系列联合查询来构建结果行，每个条件一个查询。

这构建了测试数据：

INSERT INTO TData
    ([ID], [FName], [LName], [Status], [StartTime], [EndTime], [Hours], [Location])
VALUES
    (101, 'Andrew', 'Smith', 'Available', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-19 09:00:00', '2014-08-19 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-20 12:00:00', '2014-08-20 19:00:00', 7.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-20 09:00:00', '2014-08-20 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-21 09:00:00', '2014-08-21 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-21 04:00:00', '2014-08-21 12:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-22 06:00:00', '2014-08-22 18:00:00', 12.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-22 09:00:00', '2014-08-22 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-23 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-23 11:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-24 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-24 09:00:00', '2014-08-23 14:00:00', 3.00, 'London'),
    (101, 'Andrew', 'Smith', 'Available', '2014-08-25 09:00:00', '2014-08-23 17:00:00', 8.00, 'London'),
    (101, 'Andrew', 'Smith', 'SICKNESS', '2014-08-25 11:00:00', '2014-08-23 17:00:00', 3.00, 'London')
;

这是一个结果的开始。您需要为最终显示的每一行/条件添加更多UNION子句。

;WITH Combined AS (
SELECT T1.*, t2.StartTime AS S_StartTime, t2.EndTime AS S_EndTime, 
        t2.Status AS S_Status, t2.Hours AS S_Hours
FROM TData T1 --Change TData to your table name
LEFT OUTER JOIN Tdata T2 ON T1.id = T2.id 
        AND CAST(T1.StartTime AS date) = CAST(T2.StartTime as date) 
        AND t1.Status <> t2.Status
WHERE T1.Status <> 'SICKNESS' 
)
--this is case 1 Show sickness only?  If not, add another row
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, s_EndTime AS EndTime, 
        S_Hours AS Hours, Location
FROM combined 
WHERE StartTime = S_StartTime and EndTime = s_EndTime 
UNION --Case 2 Row 1
SELECT ID, FName, LName, s_Status AS Status, 
        s_StartTime AS StartTime, StartTime AS EndTime, 
        DATEDIFF(minute,S_StartTime,StartTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 2
SELECT ID, FName, LName, S_Status AS Status, 
       StartTime AS StartTime, S_EndTime AS EndTime, 
       DATEDIFF(minute,StartTime,S_EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime 
UNION --case 2 row 3
SELECT ID, FName, LName, Status, 
       S_EndTime AS StartTime, EndTime AS EndTime, 
       DATEDIFF(minute,S_EndTime,EndTime)/60 AS Hours, Location
FROM combined 
WHERE S_StartTime < StartTime 
      AND S_EndTime BETWEEN StartTime AND EndTime

我可以把剩下的条款写出来，但应该很清楚如何做。如果你需要额外的帮助，请告诉我。

相关内容

最新更新

热门标签：