Split time duration into days

Last Post 14 Mar 2008 06:53 AM by SwePeso. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gpsfog
New Member
New Member

--
13 Mar 2008 04:47 AM
Hello, I was looking for various ideas on how to split a time duration that spans up to several days into individual "day parts" for the purpose of summary reporting. For example, a system outage time from 15 Feb 08 12:15:31 to 18 Feb 08 02:31:12. The "query" would break down the outage into component parts on each day (15 Feb 08 12:15:31 to 16 Feb 08 00:00:00, 16 Feb 08 00:00:00 to 17 Feb 08 00:00:00, 17 Feb 08 00:00:00 to 18 Feb 08 00:00:00, and 18 Feb 08 00:00:00 to 18 Feb 08 02:31:12). Currently I am using a cursor to iterate through the outage records, and then custom logic in a loop to break out each day part and add it to a temporary table which is then returned. I am looking for other, more efficient ideas.

Thank you
SwePeso
New Member
New Member

--
14 Mar 2008 06:53 AM
DECLARE @Sample TABLE (ID INT, dt1 DATETIME, dt2 DATETIME)

INSERT @Sample
SELECT 1, '15 Feb 08 12:15:31', '18 Feb 08 02:31:12' UNION ALL
SELECT 2, '15 May 07 12:15:31', '18 Jun 07 02:31:12'

;WITH Yak (ID, fromDt, toDt, maxDt)
AS (
SELECT ID,
dt1,
CASE
WHEN DATEPART(HOUR, dt1) < 8 THEN DATEADD(DAY, DATEDIFF(DAY, '19000101', dt1), '19000101 08:00')
ELSE DATEADD(DAY, DATEDIFF(DAY, '19000101', dt1), '19000102 08:00')
END,
dt2
FROM @Sample

UNION ALL

SELECT ID,
toDt,
dateadd(day, 1, toDt),
maxDt
FROM Yak
WHERE toDt < maxDt
)

SELECT ID,
fromDt,
CASE
WHEN toDt > maxDt THEN maxDt
ELSE toDt
END AS toDt
FROM Yak
ORDER BY ID,
fromDt
SwePeso
New Member
New Member

--
14 Mar 2008 06:57 AM
Output from above code is

ID fromDt toDt
1 2008-02-15 12:15:31.000 2008-02-16 08:00:00.000
1 2008-02-16 08:00:00.000 2008-02-17 08:00:00.000
1 2008-02-17 08:00:00.000 2008-02-18 02:31:12.000
2 2007-05-15 12:15:31.000 2007-05-16 08:00:00.000
2 2007-05-16 08:00:00.000 2007-05-17 08:00:00.000
2 2007-05-17 08:00:00.000 2007-05-18 08:00:00.000
2 2007-05-18 08:00:00.000 2007-05-19 08:00:00.000
2 2007-05-19 08:00:00.000 2007-05-20 08:00:00.000
2 2007-05-20 08:00:00.000 2007-05-21 08:00:00.000
2 2007-05-21 08:00:00.000 2007-05-22 08:00:00.000
2 2007-05-22 08:00:00.000 2007-05-23 08:00:00.000
2 2007-05-23 08:00:00.000 2007-05-24 08:00:00.000
2 2007-05-24 08:00:00.000 2007-05-25 08:00:00.000
2 2007-05-25 08:00:00.000 2007-05-26 08:00:00.000
2 2007-05-26 08:00:00.000 2007-05-27 08:00:00.000
2 2007-05-27 08:00:00.000 2007-05-28 08:00:00.000
2 2007-05-28 08:00:00.000 2007-05-29 08:00:00.000
2 2007-05-29 08:00:00.000 2007-05-30 08:00:00.000
2 2007-05-30 08:00:00.000 2007-05-31 08:00:00.000
2 2007-05-31 08:00:00.000 2007-06-01 08:00:00.000
2 2007-06-01 08:00:00.000 2007-06-02 08:00:00.000
2 2007-06-02 08:00:00.000 2007-06-03 08:00:00.000
2 2007-06-03 08:00:00.000 2007-06-04 08:00:00.000
2 2007-06-04 08:00:00.000 2007-06-05 08:00:00.000
2 2007-06-05 08:00:00.000 2007-06-06 08:00:00.000
2 2007-06-06 08:00:00.000 2007-06-07 08:00:00.000
2 2007-06-07 08:00:00.000 2007-06-08 08:00:00.000
2 2007-06-08 08:00:00.000 2007-06-09 08:00:00.000
2 2007-06-09 08:00:00.000 2007-06-10 08:00:00.000
2 2007-06-10 08:00:00.000 2007-06-11 08:00:00.000
2 2007-06-11 08:00:00.000 2007-06-12 08:00:00.000
2 2007-06-12 08:00:00.000 2007-06-13 08:00:00.000
2 2007-06-13 08:00:00.000 2007-06-14 08:00:00.000
2 2007-06-14 08:00:00.000 2007-06-15 08:00:00.000
2 2007-06-15 08:00:00.000 2007-06-16 08:00:00.000
2 2007-06-16 08:00:00.000 2007-06-17 08:00:00.000
2 2007-06-17 08:00:00.000 2007-06-18 02:31:12.000
You are not authorized to post a reply.

Acceptable Use Policy