Half Hour Impacts
Yesterday I came across this question on another forum.
I am trying to come up with a way to identify the half hour impact from
several exceptions across multiple days. I have access to SQL 2000 and SQL 2005.
The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM),
stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact.
When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see the following impacts:
7:00 - .5
7:30 - 1
8:00 - .66
When I have another exception with a start time 7:30 and an end time of 9:15, I would like to see the following impacts:
7:30 – 1
8:00 – 1
8:30 – 1
9:00 - .5
Then when I roll them all up, I would see the following:
7:00 - .5
7:30 – 2
8:00 – 1.66
8:30 – 1
9:00 - .5
What would be the best approach to get these results?
several exceptions across multiple days. I have access to SQL 2000 and SQL 2005.
The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM),
stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact.
When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see the following impacts:
7:00 - .5
7:30 - 1
8:00 - .66
When I have another exception with a start time 7:30 and an end time of 9:15, I would like to see the following impacts:
7:30 – 1
8:00 – 1
8:30 – 1
9:00 - .5
Then when I roll them all up, I would see the following:
7:00 - .5
7:30 – 2
8:00 – 1.66
8:30 – 1
9:00 - .5
What would be the best approach to get these results?
After some thinking (and correction by Pootle), I posted this solution today
DECLARE @Sample TABLE
(
startTime DATETIME,
endTime DATETIME
)
INSERT @Sample
(
startTime,
endTime
)
SELECT '07:15', '08:20' UNION ALL
SELECT '07:30', '09:15'
;WITH Yak(slotTime, part)
AS (
SELECT DATEADD(MINUTE, 30 * t.c + DATEDIFF(MINUTE, '00:00', s.startTime) / 30 * 30, 0),
CASE
WHEN t.e = 0 THEN DATEDIFF(MINUTE, s.startTime, s.endTime)
WHEN t.c = 0 THEN 30 - DATEPART(MINUTE, s.startTime) % 30
WHEN t.c = t.e THEN DATEDIFF(MINUTE, '00:00', s.endTime) - t.q * 30
ELSE 30.0E
END / 30.0E
FROM @Sample AS s
CROSS APPLY (
SELECT number,
COUNT(*) OVER (PARTITION BY (SELECT 0)) - 1,
DATEDIFF(MINUTE, '00:01', s.endTime) / 30
FROM master..spt_values AS v
WHERE Type = 'P'
AND number <= DATEDIFF(MINUTE, '00:01', s.endTime) / 30 - DATEDIFF(MINUTE, '00:00', s.startTime) / 30
) AS t(c, e, q)
)
SELECT slotTime,
SUM(part) AS impact
FROM Yak
GROUP BY slotTime
ORDER BY slotTime