CODEWITHSUNDEEP
sqldatedatetimesql-server-2008-r2
mark1234
mark1234

Reputation: 1121

splitting overlapping dates in SQL

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

Upvotes: 1

Views: 1495

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13096

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

Upvotes: 3

Related Questions