Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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.

Powered by:
Powered By Subtext Powered By ASP.NET