Multiple rows selected from a date ranged row

Question

Table example:

TableName:DownTime

Columns:
EventID - int,
ReasonID - int,
StartTime - DateTime,
EndTime - DateTime

I would like to calculate the daily downtime of each event over the last 30 days. However, an event could be down for multiple days, so the start time would could start 31 days prior and could end next week. So I need to return a row of data for that event 30 times. The event could also be 10 minutes long, so I need a row of data showing that.

So far I only get one row representing the entire event that occurs over 30 days instead of 30 rows.

MS SQL Server 2005 database

Thank you for any help.

Answer 1

before you try my code, you need to do a one time setup of a Numbers table. this will create a table names Numbers with a column Number having rows with values from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

This Numbers table is joined to your existing table to "create" the rows you need, try this:

DECLARE @DownTime table (
EventID  int,
ReasonID  int,
StartTime  DateTime,
EndTime  DateTime)

INSERT INTO @DownTime VALUES (1,1,'9/10/2009 9:00am','9/10/2009 10:00am')
INSERT INTO @DownTime VALUES (2,1,'9/10/2009 1:00am','9/15/2009  1:00am')
INSERT INTO @DownTime VALUES (3,1,'9/10/2009'       ,'9/11/2009')

SELECT
    d.EventID,d.ReasonID,d.StartTime,d.EndTime
        , DATEADD(day,Number-1,d.StartTime) AS SequenceDate
    FROM @DownTime          d
        INNER JOIN Numbers  n ON n.Number<=DATEDIFF(day,d.StartTime,d.EndTime)+1

OUTPUT:

EventID ReasonID StartTime               EndTime                 SequenceDate
------- -------- ----------------------- ----------------------- -----------------------
1       1        2009-09-10 09:00:00.000 2009-09-10 10:00:00.000 2009-09-10 09:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-10 01:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-11 01:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-12 01:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-13 01:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-14 01:00:00.000
2       1        2009-09-10 01:00:00.000 2009-09-15 01:00:00.000 2009-09-15 01:00:00.000
3       1        2009-09-10 00:00:00.000 2009-09-11 00:00:00.000 2009-09-10 00:00:00.000
3       1        2009-09-10 00:00:00.000 2009-09-11 00:00:00.000 2009-09-11 00:00:00.000

(9 row(s) affected)

Answer 2

You may want to create a date dimension table - a table that is a collection of days. The absolute simplest example for your purposes would have one column, date, and you would have one row for every consecutive day. Youu could then join this table to your downtime table:

from dim_date t join downtime d on t.date between convert(varchar(10), d.start_date, 120) and convert(varchar(10), d.end_date, 120)

This way if you have down time, you will get a row for every day containing down time.