split timedate range at intersection
-
Hi Guys
Using SQL Server 2008 R2
I'm trying to create a report and chart for a a manufacturing resource's activity for a given 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. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). 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. 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
Below is some sample data
[font="Courier New"]
|--------------------------J1------------------------------------| running
|----D1-----| |-------D2-------| down
|--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running
|----D1-------| down
|-----------------J1-----------------------| |----D1-------| result[/font]
Can someone point me in the right direction?
Thanks
Mark
-
not sure a better solution but it's a solution
;WITH cte AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ResourceID, dt) AS Rno,
x.ResourceID, x.JobNo, Dt, xdt.Type
FROM (
SELECT j.ResourceID, j.JobNo, j.startdate, j.enddate, 'P' AS JType
FROM #Jobs AS j
UNION ALL
SELECT d.ResourceID, d.Reason, d.startdate, d.enddate, 'D' AS Jtype
FROM #Downtime AS d
) AS x
CROSS APPLY
(
VALUES (x.startdate, x.JType), (x.enddate, x.JType)
) AS xdt(Dt, Type)
)
SELECT x.ResourceID,
CASE WHEN x.JobNo > x1.JobNo THEN x.JobNo ELSE x1.JobNo END AS Activity,
x.dt AS StartDate, x1.Dt AS EndDate,
CASE WHEN x.Type > x1.Type THEN x.Type ELSE x1.type END AS activitytype
FROM cte AS x
LEFT OUTER JOIN cte AS x1
ONx.ResourceID = x1.ResourceID AND x.Rno = x1.Rno - 1
WHERE x1.Dt IS NOT NULL AND x1.Dt <> x.Dt;
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (4/17/2014)
not sure a better solution but it's a solution;WITH cte AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ResourceID, dt) AS Rno,
x.ResourceID, x.JobNo, Dt, xdt.Type
FROM (
SELECT j.ResourceID, j.JobNo, j.startdate, j.enddate, 'P' AS JType
FROM #Jobs AS j
UNION ALL
SELECT d.ResourceID, d.Reason, d.startdate, d.enddate, 'D' AS Jtype
FROM #Downtime AS d
) AS x
CROSS APPLY
(
VALUES (x.startdate, x.JType), (x.enddate, x.JType)
) AS xdt(Dt, Type)
)
SELECT x.ResourceID,
CASE WHEN x.JobNo > x1.JobNo THEN x.JobNo ELSE x1.JobNo END AS Activity,
x.dt AS StartDate, x1.Dt AS EndDate,
CASE WHEN x.Type > x1.Type THEN x.Type ELSE x1.type END AS activitytype
FROM cte AS x
LEFT OUTER JOIN cte AS x1
ONx.ResourceID = x1.ResourceID AND x.Rno = x1.Rno - 1
WHERE x1.Dt IS NOT NULL AND x1.Dt <> x.Dt;
Thanks SSC Vet!
Tried your solution and it works for some cases. I updated the sample data with a case where the job can finish before DT starts, but I still need to show the down time events
I added a graphical example too
-
Same logic slightly changed
;WITH cte AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ResourceID, dt) AS Rno,x.ResourceID, x.Activity, Dt, opr, jobno
FROM (
SELECT j.ResourceID, d.Reason AS Activity, d.startdate, d.enddate, j.JobNo, j.startdate as JOBStart,j.enddate AS jobend
FROM #Jobs j LEFT JOIN #Downtime AS d
ON j.ResourceID = d.ResourceID AND
j.startdate<d.startdate AND
j.startdate<d.enddate AND
j.enddate>d.startdate AND
j.enddate>d.enddate
) AS x
CROSS APPLY
(
VALUES (x.JOBStart, 2,'P'), (x.jobend, 3,'P'), (x.startdate, 0,'D'), (x.enddate, 1,'D')
) AS xdt(Dt, opr, Jtype)
)
SELECT x.ResourceID,
CASE WHEN x.opr in(1,2) THEN x1.jobno
ELSE x.Activity END AS Activity,
x.dt AS StartDate, x1.Dt AS EndDate,
CASE WHEN x.opr in(1,2) THEN 'p' ELSE 'D' END AS activitytype
FROM cte AS x
LEFT OUTER JOIN cte AS x1
ONx.ResourceID = x1.ResourceID AND x.Rno = x1.Rno - 1
WHERE x1.Dt IS NOT NULL AND x1.Dt <> x.Dt
UNION ALL
SELECT d.ResourceID, d.Reason, d.startdate, d.enddate, 'D' AS Jtype
FROM #Downtime AS d
WHERE NOT EXISTS (SELECT 1 FROM #Jobs j
WHERE j.ResourceID = d.ResourceID AND
j.startdate<d.startdate AND
j.startdate<d.enddate AND
j.enddate>d.startdate AND
j.enddate>d.enddate
)
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
here's the approach I came up with returning exactly the rows you requested.
Depending on the original data size it might be better to store the results of cte_production_slots in an intermediate table before joining it to the jobs table. It would also improve the performance if it wouldn't be required to scan both, #jobs and #Downtime to find the total production time...
Usually, there would be a shift plan table holding the general production time including scheduled breaks.
This would allow to build the production_slots table in advance and "just" add the unscheduled downtime as additional non-production slots.
;WITH cte_production_range as
(-- find the production time per resource including the production duration
SELECT ResourceID, min(startdate) min_start, max(enddate)max_end, datediff(hh, min(startdate), max(enddate)) as diff
FROM
(
SELECT ResourceID, startdate, enddate FROM #jobs
UNION ALL
SELECT ResourceID, startdate, enddate FROM #Downtime
)x
GROUP BY ResourceID
),
cte_production_time as
(-- build a numbered time table with slot_begin and end time on a hourly base
SELECT
cte_production_range.ResourceID,
ROW_NUMBER() OVER(PARTITION BY cte_production_range.ResourceID ORDER BY number) as pos,
dateadd(hh,number,cte_production_range.min_start) as production_time ,
dateadd(hh,number+1,cte_production_range.min_start) as production_time2
FROM cte_production_range
CROSS APPLY
(SELECT TOP(cte_production_range.diff) number FROM master..spt_values WHERE type='P' ORDER BY number)y
),
cte_production_activity as
(-- find the down time per resource and a second group of numbers
SELECT pt.*, LEFT(dt.Reason,1) AS activityType, dt.Reason as activity,
ROW_NUMBER() OVER(PARTITION BY pt.ResourceID,LEFT(dt.Reason,1) ORDER BY pos) AS pos2
FROM cte_production_time pt
LEFT OUTER JOIN #Downtime dt
ON pt.ResourceID = dt.ResourceID
AND pt.production_time >= dt.startdate
AND pt.production_time < dt.enddate
),
cte_production_slots AS
(-- build the production slots
SELECT
ResourceId, min(production_time) slot_begin,max(production_time2) slot_end,activity,activityType
FROM cte_production_activity pa
GROUP BY ResourceId,activity,pos-pos2,activityType
)
-- join the production slots to the jobs table and build the final result set
SELECT ps.ResourceId, ISNULL(ps.activity,j.jobno) AS activity,
ISNULL(CASE WHEN startdate < slot_begin THEN slot_begin ELSE startdate end,slot_begin) AS startdate ,
ISNULL(CASE WHEN enddate > slot_end THEN slot_end ELSE enddate end,slot_end) AS enddate,
ISNULL(ps.activityType,'P') AS activityType
FROM cte_production_slots ps
LEFT OUTER JOIN #jobs j ON ps.ResourceId =j.ResourceId AND ps.slot_begin < j.enddate AND ps.slot_end > j.startdate
WHERE ISNULL(ps.activity,j.jobno) IS NOT NULL
ORDER BY ps.ResourceID, slot_begin, jobno
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply