splitting overlapping dates in SQL

Question

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

Which platform and version is your db (although it's probably one of the SQL Server distros, which one)? You mention dates, but your data only differs in time, which is it? Are you trying to `INSERT` the data into `#Jobs` and `#Downtime`, or just build the report? A Calendar table will be immensely helpful here - you have one, right? What have you tried so far that didn't work? Thanks for nicely setup starting/result data, though. (As a side note, end-dates are almost always derived information, being the instant some other state starts). — Clockwork-Muse, Apr 18 '14 at 02:15
@Clockwork-Muse Thanks for reply. Can't believe I forgot to mention platform. So far I've tried using multiple CTE's, combining everything into a single table - none of them got close, so didn;t think it was worth posting — mark1234, Apr 18 '14 at 12:05
Forgot to mention about the end time. You're right, normally you have to calculate based on start duration and down times. but in this case the end time is supplied with the downtime factored in. Almost like it's historical data: where J1 started at Time A and finished at Time B (including downtime). Downtime's were recorded separately — mark1234, Apr 18 '14 at 12:13
Well, what did you try that didn't work, and how did it fail you? I'm still confused as to if you're attempting to build entries for the two tables, or just need a `SELECT` statement (to build `#Results`, essentially). — Clockwork-Muse, Apr 18 '14 at 13:00
@Clockwork-Muse. I added my latest result and details of failure. I have the two tables (Jobs and Downtime) and trying to build Results (i.e. the activity on ResourceX for a given date range-- Result in my "pictures" above) — mark1234, Apr 18 '14 at 13:53
It's failing for J3 too, as it's marking the middle instance as downtime instead of as on a job. Okay, you're essentially looking for assignments per `resourceId` (not by job), with priority going to downtime...hmmm... — Clockwork-Muse, Apr 19 '14 at 00:54

score 3 · Accepted Answer · edited May 23 '17 at 10:32

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

Thanks a lot! You helped to solve my question https://stackoverflow.com/questions/69901792/postgresql-merge-rows-versioned-by-date-ranges-with-priority — Dmitry K., Nov 10 '21 at 14:05

splitting overlapping dates in SQL

1 Answers1

Linked