Sql-server – Extrapolating gaps in hourly data

sql server

I have data in the form of

 DateTime -- Status  --   Duration Seconds 
 12:00:00,   Available,   10800
 15:00:00,   Unavailable, 460

I need to translate that into

 DateTime -- Status
 12:00:00,   Available
 13:00:00,   Available
 14:00:00,   Available
 15:00:00,   Unavailable

The logic to extrapolate the data this way is simple, but translating it into SQL is proving to be difficult. Any assistance would be greaty appreciated!

Best Answer

Since it looks like you only want a data point every hour, one approach could be to use the durationSeconds column to figure out how many subsequent hour boundaries the data point should cover. The following query performs this logic and yields your desired results on the test data.

-- Create demo data
CREATE TABLE #status (
    DateTime DATETIME NOT NULL,
    Status VARCHAR(20) NOT NULL,
    DurationSeconds INT NOT NULL
)
GO
INSERT INTO #status (DateTime, Status, DurationSeconds)
VALUES ('2015-07-08 12:00:00', 'Available', 10800),
        ('2015-07-08 15:00:00', 'Unavailable', 460)
GO

-- Extrapolate the gaps!
-- Tally table taken from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
; WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
        t2 AS (SELECT 1 N FROM t1 x, t1 y),     
        t3 AS (SELECT 1 N FROM t2 x, t2 y),
        t4 AS (SELECT 1 N FROM t3 x, t3 y),
        TallyHours AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)
SELECT sExtrapolated.DateTime, sExtrapolated.Status
FROM #status s
CROSS APPLY (
    -- The original status
    SELECT s.DateTime, s.Status
    UNION ALL
    -- Any extrapolated gaps; if the DurationSeconds pushes past the next hour boundary,
    -- we infer that we should fill in the gap.  If you need slightly different business logic,
    -- you can adjust this part accordingly
    SELECT DATEADD(hh, h.N, s.DateTime), s.Status
    FROM TallyHours h
    WHERE h.N < CEILING(s.DurationSeconds / 3600.)
) sExtrapolated
GO