在SQL中拆分重叠的日期

我在SQLServer2008R2上

我正试图为给定时期（通常为30-90天）的制造资源活动创建一份报告和图表

作业是为运行的长度（例如4天）创建的。如果周末没有工作，而上述作业在周五开始，则资源的活动需要显示1天运行、2天停机、3天运行，而生产调度程序不必将其设为两个作业。我在一个表中有作业的时间表，在另一个表上有停机时间（所以把DT想象成某种日历表）。不同寻常的是，提供的结束时间考虑了停机时间。

因此，我需要该查询为该作业创建3个日期时间范围：周五运行、周六、周日运行、周一、周二、周三运行。注意：一个作业可能有多个停机事件。

我在这个问题上兜圈子已经有一段时间了。我相信有一个很好的方法：我就是找不到它。我已经找到了几个类似的帖子，但都不能应用到我的案例中（或者至少不能让它们发挥作用）

以下是一些样本日期和预期结果。我希望解释和示例数据是清楚的。

-- Create tables to work with / Source and Destination
CREATE TABLE #Jobs
    (
     ResourceID int
    ,JobNo VARCHAR(10)
    ,startdate SMALLDATETIME
    ,enddate SMALLDATETIME
    )

CREATE TABLE #Downtime
    (
     ResourceID INT
    ,Reason VARCHAR(10)
     ,startdate SMALLDATETIME
    ,enddate SMALLDATETIME
    )

CREATE TABLE #Results
    (
    ResourceID INT
    ,Activity VARCHAR(10)
    ,startdate SMALLDATETIME
    ,enddate SMALLDATETIME
    ,ActivityType  varchar(1)
    )

-- Job Schedule
INSERT INTO [#Jobs] 
(
[ResourceID],
[JobNo],
startdate
,enddate
)
SELECT 1, 'J1', '2014-04-01 08:00' ,'2014-04-01 17:00'
UNION ALL
SELECT 1, 'J2', '2014-04-01 17:00' , '2014-04-01 23:00'
UNION ALL
SELECT 2, 'J3', '2014-04-01 08:00' ,'2014-04-01 23:00'
UNION ALL
SELECT 3, 'J4', '2014-04-01 08:00' ,'2014-04-01 09:00'
SELECT * FROM #jobs

-- Downtime Scehdule
INSERT INTO [#Downtime] 
(
[ResourceID],
Reason,
startdate
,enddate
)
SELECT  1, 'DOWN', '2014-04-01 10:00' ,'2014-04-01 11:00'
UNION ALL
SELECT  1, 'DOWN', '2014-04-01 21:00' , '2014-04-01 22:00'
UNION ALL
SELECT  2, 'DOWN', '2014-04-01 10:00' ,'2014-04-01 11:00'
UNION ALL
SELECT  2, 'DOWN',  '2014-04-01 21:00' , '2014-04-01 22:00'
 UNION ALL
SELECT  3, 'DOWN', '2014-04-01 10:00' ,'2014-04-01 11:00'
UNION ALL
SELECT  3, 'DOWN',  '2014-04-01 21:00' , '2014-04-01 22:00'

SELECT * FROM #Downtime
-- Expected Results
INSERT INTO [#Results] 
(
Activity,
[ResourceID],
startdate
,enddate
,[ActivityType]
)
SELECT 'J1', 1, '2014-04-01 08:00' ,'2014-04-01 10:00', 'P'
UNION ALL
SELECT 'DOWN', 1, '2014-04-01 10:00' , '2014-04-01 11:00', 'D'
UNION ALL
SELECT 'J1', 1, '2014-04-01 11:00' ,'2014-04-01 17:00', 'P'
UNION ALL
SELECT 'J2', 1, '2014-04-01 17:00' , '2014-04-01 21:00', 'P'
UNION ALL
SELECT 'DOWN', 1, '2014-04-01 21:00' , '2014-04-01 22:00', 'D'
UNION ALL
SELECT 'J2', 1, '2014-04-01 22:00' ,'2014-04-01 23:00', 'P'
UNION ALL
SELECT 'J3', 2, '2014-04-01 08:00' ,'2014-04-01 10:00', 'P'
UNION ALL
SELECT 'DOWN', 2, '2014-04-01 10:00' , '2014-04-01 11:00', 'D'
UNION ALL
SELECT 'J3', 2, '2014-04-01 11:00' ,'2014-04-01 21:00', 'P'
UNION ALL
SELECT 'DOWN', 2, '2014-04-01 21:00' , '2014-04-01 22:00', 'D'
UNION ALL
SELECT 'J3', 2, '2014-04-01 22:00' ,'2014-04-01 23:00', 'P'
 UNION ALL
SELECT 'J4', 3, '2014-04-01 08:00' ,'2014-04-01 09:00', 'P'
UNION ALL
SELECT 'DOWN', 3, '2014-04-01 10:00' , '2014-04-01 11:00', 'D'
UNION ALL
SELECT 'DOWN', 3, '2014-04-01 21:00' , '2014-04-01 22:00', 'D'

SELECT * FROM #Results
ORDER BY [ResourceID], Startdate

DELETE FROM  #Results

|--------------------------J1------------------------------|正在运行|----D1----||-----D2-----|下降|--J1-|-----D1------J1-----|-----D2------J1--------|结果

|-----------------------------J1---------------|运行|----D1-------|向下|-----------------J1------------------------||----D1------------------------|结果

有人能给我指正确的方向吗？

这是我最近的一次。当有重叠时效果很好，但在J4上失败，在停机之前作业结束

WITH    cte
      AS ( SELECT
            ROW_NUMBER() OVER ( ORDER BY ResourceID, dt ) AS Rno
           ,x.ResourceID
           ,x.Activity
           ,Dt
           ,xdt.ActivityType
           FROM
           (
            SELECT     
                ResourceID
               ,JobNo AS Activity
               ,startdate
               ,enddate 
               ,'P' AS ActivityType
               FROM #Jobs
            UNION ALL
            SELECT     
                ResourceID
               ,Reason AS Activity
               ,startdate
               ,enddate 
               ,'D' AS ActivityType
               FROM #Downtime 
             ) AS x
            CROSS APPLY 
            ( 
                VALUES ( x.startdate, x.ActivityType),
                        ( x.enddate, x.ActivityType) ) AS xdt 
                ( Dt, ActivityType )
         )
SELECT
    x.ResourceID
   ,CASE WHEN x.Activity > x1.Activity THEN x.Activity
         ELSE x1.Activity
    END AS Activity
   ,x.dt AS StartDate
   ,x1.Dt AS EndDate
   ,CASE WHEN x.ActivityType > x1.ActivityType THEN x.ActivityType
         ELSE x1.ActivityType
    END AS activitytype
FROM
    cte AS x
    LEFT OUTER JOIN cte AS x1 ON x.ResourceID = x1.ResourceID
                                 AND x.Rno = x1.Rno - 1
WHERE
    x1.Dt IS NOT NULL
    AND x1.Dt <> x.Dt;

感谢

标记

实际上，您已经非常接近了——与其在最初的CTE中做所有事情，不如稍后再连接回原始数据。从本质上讲，您正在对此处提供的答案执行变体
以下查询应该能为您提供所需信息：

WITH AllDates AS (SELECT a.*, ROW_NUMBER() OVER(PARTITION BY resourceId ORDER BY rangeDate) AS rn
                  FROM (SELECT resourceId, startDate
                        FROM Jobs
                        UNION ALL
                        SELECT resourceId, endDate
                        FROM Jobs
                        UNION ALL
                        SELECT resourceId, startDate
                        FROM Downtime
                        UNION ALL
                        SELECT resourceId, endDate
                        FROM DownTime) a(resourceId, rangeDate)),
 Range AS (SELECT startRange.resourceId,
                  startRange.rangeDate AS startDate, endRange.rangeDate AS endDate
           FROM AllDates startRange
           JOIN AllDates endRange
             ON endRange.resourceId = startRange.resourceId
                AND endRange.rn = startRange.rn + 1
                AND endRange.rangeDate <> startRange.rangeDate)
SELECT Range.resourceId, Range.startDate, Range.endDate, 
       COALESCE(Downtime.reason, Jobs.jobNo) as activity
FROM Range
LEFT JOIN Jobs
       ON Jobs.resourceId = Range.resourceId
          AND Jobs.startDate <= Range.startDate
          AND Jobs.endDate >= Range.endDate
LEFT JOIN Downtime
       ON Downtime.resourceId = Range.resourceId
          AND Downtime.startDate <= Range.startDate
          AND Downtime.endDate >= Range.endDate
WHERE Jobs.jobNo IS NOT NULL
      OR Downtime.reason IS NOT NULL

（还有小提琴。这实际上应该是ANSI标准的SQL）
…产生预期：

RESOURCEID   STARTDATE             ENDDATE               ACTIVITY
----------------------------------------------------------------------------
1            2014-04-01 08:00:00   2014-04-01 10:00:00   J1 
1            2014-04-01 10:00:00   2014-04-01 11:00:00   DOWN 
1            2014-04-01 11:00:00   2014-04-01 17:00:00   J1 
1            2014-04-01 17:00:00   2014-04-01 21:00:00   J2 
1            2014-04-01 21:00:00   2014-04-01 22:00:00   DOWN 
1            2014-04-01 22:00:00   2014-04-01 23:00:00   J2 
2            2014-04-01 08:00:00   2014-04-01 10:00:00   J3 
2            2014-04-01 10:00:00   2014-04-01 11:00:00   DOWN 
2            2014-04-01 11:00:00   2014-04-01 21:00:00   J3 
2            2014-04-01 21:00:00   2014-04-01 22:00:00   DOWN 
2            2014-04-01 22:00:00   2014-04-01 23:00:00   J3 
3            2014-04-01 08:00:00   2014-04-01 09:00:00   J4 
3            2014-04-01 10:00:00   2014-04-01 11:00:00   DOWN 
3            2014-04-01 21:00:00   2014-04-01 22:00:00   DOWN

相关内容

最新更新

热门标签：