Thinking outside the box

Patron Saint of Lost Yaks

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?

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

Print | posted on Thursday, November 27, 2008 10:50 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Comments have been closed on this topic.