在 SQL 中分割重叠的日期 [英] splitting overlapping dates in SQL
问题描述
我使用的是 SQLServer 2008 R2
我正在尝试为特定时期(通常为 30-90 天)内制造资源的活动创建报告和图表
在运行期间(例如 4 天)创建作业.如果周末没有工作并且上述作业在星期五开始，则资源的活动需要显示 1 天运行、2 天下降、3 天运行，而生产调度程序不必使其成为两个作业.我在一张表中有工作的时间表，在另一张表中有停机时间(所以把 DT 想象成某种日历表).不同寻常的是，结束时间会包含停机时间.
所以我需要查询来为这项工作创建 3 个日期时间范围:周五跑步、周六、太阳下山、周一、周二、周三跑步.注意:单个作业可以有多个停机事件.
在这个问题上兜圈子已经有一段时间了.我敢肯定有一种优雅的方法可以做到这一点:我就是找不到.我找到了几篇类似的帖子，但无法将任何内容应用于我的案例(或者至少无法让它们工作)
以下是一些示例日期和预期结果.我希望解释和示例数据清楚.
-- 创建表以使用/Source 和 Destination创建表#工作(资源ID int,JobNo VARCHAR(10),开始日期 SMALLDATETIME,enddate SMALLDATETIME)创建表#停机时间(资源 ID INT,原因 VARCHAR(10),开始日期 SMALLDATETIME,enddate SMALLDATETIME)创建表#结果(资源 ID INT,活动 VARCHAR(10),开始日期 SMALLDATETIME,enddate SMALLDATETIME,ActivityType varchar(1))-- 工作安排插入 [#Jobs]([资源ID],[职位编号],开始日期，结束日期)选择 1，'J1'，'2014-04-01 08:00'，'2014-04-01 17:00'联合所有选择 1，'J2'，'2014-04-01 17:00'，'2014-04-01 23:00'联合所有选择 2，'J3'，'2014-04-01 08:00'，'2014-04-01 23:00'联合所有选择 3，'J4'，'2014-04-01 08:00'，'2014-04-01 09:00'从#jobs中选择*-- 停机时间计划插入 [#Downtime]([资源ID],原因，开始日期，结束日期)选择 1，向下"，2014-04-01 10:00"，2014-04-01 11:00"联合所有选择 1，向下"，2014-04-01 21:00"，2014-04-01 22:00"联合所有选择 2，向下"，2014-04-01 10:00"，2014-04-01 11:00"联合所有选择 2，向下"，2014 年 4 月 1 日 21:00"，2014 年 4 月 1 日 22:00"联合所有选择 3，向下"，2014-04-01 10:00"，2014-04-01 11:00"联合所有选择 3，向下"，2014-04-01 21:00"，2014-04-01 22:00"选择 * 从 #停机时间- 预期成绩插入 [#Results](活动，[资源ID],开始日期，结束日期,[活动类型])选择'J1'，1，'2014-04-01 08:00'，'2014-04-01 10:00'，'P'联合所有选择向下"、1、2014-04-01 10:00"、2014-04-01 11:00"、D"联合所有选择'J1'，1，'2014-04-01 11:00'，'2014-04-01 17:00'，'P'联合所有选择J2"、1、2014-04-01 17:00"、2014-04-01 21:00"、P"联合所有选择向下"、1、2014-04-01 21:00"、2014-04-01 22:00"、D"联合所有选择'J2'，1，'2014-04-01 22:00'，'2014-04-01 23:00'，'P'联合所有选择'J3'，2，'2014-04-01 08:00'，'2014-04-01 10:00'，'P'联合所有选择向下"、2、2014-04-01 10:00"、2014-04-01 11:00"、D"联合所有选择'J3'，2，'2014-04-01 11:00'，'2014-04-01 21:00'，'P'联合所有选择向下"、2、2014-04-01 21:00"、2014-04-01 22:00"、D"联合所有选择'J3'，2，'2014-04-01 22:00'，'2014-04-01 23:00'，'P'联合所有选择'J4'，3，'2014-04-01 08:00'，'2014-04-01 09:00'，'P'联合所有选择向下"、3、2014-04-01 10:00"、2014-04-01 11:00"、D"联合所有选择向下"、3、2014-04-01 21:00"、2014-04-01 22:00"、D"从#结果中选择 *ORDER BY [ResourceID]，开始日期从#结果中删除<上一页>|--------------------------J1------------------------|跑步|----D1-----||-------D2--------|向下|--J1----|----D1-----|--------J1------|-------D2-------|-----J1-----|结果
<上一页>|-----------------------------J1------------|跑步|----D1--------|向下|-----------------J1----------------------||----D1--------|结果
有人能指出正确的方向吗?
这是我最接近的.当有重叠时效果很好，但在 J4 上工作在停机前结束时失败
WITH cte作为(选择ROW_NUMBER() OVER (ORDER BY ResourceID, dt) AS Rno,x.ResourceID,x.活动,,xdt.ActivityType从(选择资源ID,JobNo AS 活动，开始日期，结束日期,'P' 作为活动类型来自#乔布斯联合所有选择资源ID,原因作为活动，开始日期，结束日期,'D' AS ActivityType从#停机时间) 作为 x交叉申请(值(x.startdate，x.ActivityType)，( x.enddate, x.ActivityType) ) 作为 xdt(Dt，活动类型))选择x.ResourceID,CASE WHEN x.Activity >x1.Activity THEN x.ActivityELSE x1.Activity结束活动,x.dt 作为开始日期,x1.Dt 作为结束日期,CASE WHEN x.ActivityType >x1.ActivityType THEN x.ActivityTypeELSE x1.ActivityTypeEND AS 活动类型从cte AS xLEFT OUTER JOIN cte AS x1 ON x.ResourceID = x1.ResourceID与 x.Rno = x1.Rno - 1在哪里x1.Dt 不为空AND x1.Dt <>x.Dt;谢谢
标记
您实际上已经很接近了 - 您实际上不想在最初的 CTE 中做所有事情，而是希望稍后再加入原始数据.本质上，您是在此处提供的答案上执行变体.
以下查询应该可以满足您的需求:
WITH AllDates AS (SELECT a.*, ROW_NUMBER() OVER(PARTITION BY resourceId ORDER BY rangeDate) AS rnFROM (SELECT resourceId, startDate来自乔布斯联合所有选择资源 ID，结束日期来自乔布斯联合所有选择资源 ID，开始日期从停机时间联合所有选择资源 ID，结束日期FROM DownTime) a(resourceId, rangeDate)),范围 AS(选择 startRange.resourceId，startRange.rangeDate AS startDate, endRange.rangeDate AS endDateFROM AllDates startRange加入所有日期 endRangeON endRange.resourceId = startRange.resourceIdAND endRange.rn = startRange.rn + 1AND endRange.rangeDate <>开始范围.rangeDate)选择 Range.resourceId, Range.startDate, Range.endDate,COALESCE(Downtime.reason, Jobs.jobNo) 作为活动从范围左加入工作ON Jobs.resourceId = Range.resourceIdAND Jobs.startDate <= Range.startDateAND Jobs.endDate >= Range.endDate左加入停机时间ON Downtime.resourceId = Range.resourceIdAND Downtime.startDate <= Range.startDateAND Downtime.endDate >= Range.endDateJobs.jobNo 不为空或 Downtime.reason 不为空(和 工作小提琴.这实际上应该是 ANSI 标准 SQL)
...产生预期的结果:
RESOURCEID STARTDATE ENDDATE 活动----------------------------------------------------------------------------1 2014-04-01 08:00:00 2014-04-01 10:00:00 J11 2014-04-01 10:00:00 2014-04-01 11:00:00 向下1 2014-04-01 11:00:00 2014-04-01 17:00:00 J11 2014-04-01 17:00:00 2014-04-01 21:00:00 J21 2014-04-01 21:00:00 2014-04-01 22:00:00 向下1 2014-04-01 22:00:00 2014-04-01 23:00:00 J22 2014-04-01 08:00:00 2014-04-01 10:00:00 J32 2014-04-01 10:00:00 2014-04-01 11:00:00 向下2 2014-04-01 11:00:00 2014-04-01 21:00:00 J32 2014-04-01 21:00:00 2014-04-01 22:00:00 向下2 2014-04-01 22:00:00 2014-04-01 23:00:00 J33 2014-04-01 08:00:00 2014-04-01 09:00:00 J43 2014-04-01 10:00:00 2014-04-01 11:00:00 向下3 2014-04-01 21:00:00 2014-04-01 22:00:00 向下I'm on SQLServer 2008 R2
I'm trying to create a report and chart for a a manufacturing resource's activity for a give period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. I have the jobs' schedules in one table and the downtimes in another (so think of DT like some sort of calendar table). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Note: a single job can have multiple downtime events.
Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- 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------------------------------------| running
|----D1-----| |-------D2-------| down
|--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------------------J1-----------| running
|----D1-------| down
|-----------------J1-----------------------| |----D1-------| result
Can someone point me in the right direction?
This is the closest I've got. Works great when there is an overlap, but fails on J4 where job ends before downtime
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;
Thanks
Mark
You were actually pretty close - rather than doing everything in the initial CTE, you actually want to join back to the original data later. Essentially, you're performing a variant on the answer supplied here.
The following query should get you what you need:
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
(And working fiddle. This should actually be ANSI-standard SQL)
...which yields the expected:
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
这篇关于在 SQL 中分割重叠的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
