Extract signal state during specified time frame

Question

In a SQL Server 2005 database I have a table with data logged when a signal is enabled or disabled. Simplified it looks like this:

Signal  State       Time
------  --------    ----------------
Alarm1  Disabled    2011-11-15 09:00
Alarm1  Enabled     2011-12-20 13:30
Alarm2  Enabled     2011-11-17 15:01
Alarm2  Disabled    2011-12-15 06:57
Alarm3  Disabled    2011-11-10 11:42
Alarm3  Enabled     2011-12-02 13:12
Alarm3  Disabled    2011-12-24 14:41
Alarm4  Enabled     2011-10-09 13:25
Alarm4  Disabled    2012-01-07 08:29
Alarm5  Disabled    2011-11-19 07:12
Alarm5  Enabled     2011-11-28 15:48
Alarm6  Disabled    2011-12-14 17:29
Alarm6  Enabled     2011-12-23 23:46

(All rows also have an ID column with a uniqueidentifier.)

Using T-SQL I want to extract information on which alarms was disabled during a given time frame, e.g. December 2011. The result should not only consist of those alarms that were logged as disabled that month, but also include alarms that were disabled earlier and not enabled during the given time frame.

Given the data above I would like to extract something like:

Signal  Disabled            Enabled
------  ----------------    -------
Alarm1  -                   2011-12-20 12:30
Alarm2  2011-12-15 06:57    -
Alarm3  -                   2011-12-02 13:12
Alarm3  2011-12-24 14:41    -
Alarm6  2011-12-14 17:29    2011-12-23 23:46

Any help appreciated!

Answer 1

Simplified types.

DECLARE @t TABLE (AlarmId INT NOT NULL, State BIT NOT NULL, TIME SMALLDATETIME NOT NULL)

INSERT @t
VALUES
(1  ,0,'2011-11-15 09:00')
,(1  ,1,'2011-12-20 13:30')
,(2  ,1,'2011-11-17 15:01')
,(2  ,0,'2011-12-15 06:57')
,(3  ,0,'2011-11-10 11:42')
,(3  ,1,'2011-12-02 13:12')
,(3  ,0,'2011-12-24 14:41')
,(4  ,1,'2011-10-09 13:25')
,(4  ,0,'2012-01-07 08:29')
,(5  ,0,'2011-11-19 07:12')
,(5  ,1,'2011-11-28 15:48')
,(6  ,0,'2011-12-14 17:29')
,(6  ,1,'2011-12-23 23:46')


DECLARE @Start DATETIME = '20111201'

--Initial State DISABLED
;WITH Disabled(AlarmId, Time) AS
(
    SELECT 
        AlarmId,
        Time
    FROM
    (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY AlarmId ORDER BY Time DESC) Ordinal,
            AlarmId,
            Time,
            State
        FROM @t
        WHERE 
            Time < @Start
    ) t
    WHERE 
        t.Ordinal = 1
    AND t.STate = 0
    UNION
    SELECT
        AlarmId,
        TIME
    FROM @t
    WHERE 
        Time >= @start AND Time < DATEADD(m,1,@STart)
    AND STate = 0

),
Enabled(AlarmId, Time) AS
(
    SELECT 
        AlarmId,
        TIME
    FROM @t
    WHERE 
        Time >= @start AND Time < DATEADD(m,1,@STart)
    AND STate = 1
),
Alarms(AlarmId) AS
(
    SELECT DISTINCT AlarmId FROM @t
)

SELECT 
    Alarms.AlarmId,
    CASE WHEN Disabled.Time >= @start AND Disabled.Time < DATEADD(m,1,@STart) THEN Disabled.Time ELSE NULL END Disabled,
    CASE WHEN Enabled.Time < Disabled.Time THEN NULL ELSE Enabled.Time END Enabled
FROM Alarms 
LEFT JOIN Enabled
    ON Alarms.AlarmId = Enabled.AlarmId
LEFT JOIN Disabled
    ON Alarms.AlarmId = Disabled.AlarmId
WHERE COALESCE(Enabled.Time, Disabled.Time) IS NOT NULL

Answer 2

I have two suggestions (depending on what data you want)

First some test data:

DECLARE @tbl TABLE(Signal VARCHAR(100),State  VARCHAR(100),Time DATETIME)
INSERT INTO @tbl
SELECT 'Alarm1','Disabled','2011-11-15 09:00'
UNION ALL
SELECT 'Alarm1','Enabled','2011-12-20 13:30'
UNION ALL
SELECT 'Alarm2','Enabled','2011-11-17 15:01'
UNION ALL
SELECT 'Alarm2','Disabled','2011-12-15 06:57'
UNION ALL
SELECT 'Alarm3','Disabled','2011-11-10 11:42'
UNION ALL
SELECT 'Alarm3','Enabled','2011-12-02 13:12'
UNION ALL
SELECT 'Alarm3','Disabled','2011-12-24 14:41'
UNION ALL
SELECT 'Alarm4','Enabled','2011-10-09 13:25'
UNION ALL
SELECT 'Alarm4','Disabled','2012-01-07 08:29'
UNION ALL
SELECT 'Alarm5','Disabled','2011-11-19 07:12'
UNION ALL
SELECT 'Alarm5','Enabled','2011-11-28 15:48'
UNION ALL
SELECT 'Alarm6','Disabled','2011-12-14 17:29'
UNION ALL
SELECT 'Alarm6','Enabled','2011-12-23 23:46'

DECLARE @startDate DATETIME='2011-12-01'

Then if you what the alarm not unique. I would do something like this:

SELECT
    tbl.Signal,
    CASE 
        WHEN tbl.State='Disabled'
        THEN convert(varchar, tbl.Time, 120)
        ELSE '-'
    END AS DisabledTime,
    CASE 
        WHEN tbl.State='Enabled'
        THEN convert(varchar, tbl.Time, 120)
        ELSE '-'
    END AS EnabledTime
FROM
    @tbl AS tbl
WHERE 
    tbl.Time BETWEEN @startDate AND (DATEADD(MM,1,@startDate)-1)

If you what the alarms unique the I would do something like this:

SELECT
    pvt.Signal,
    ISNULL(pvt.Disabled,'-') AS Disabled,
    ISNULL(pvt.Enabled,'-') AS Enabled
FROM
(
    SELECT
        tbl.Signal,
        convert(varchar, tbl.Time, 120) AS Time,
        tbl.State
    FROM
        @tbl AS tbl
    WHERE 
        tbl.Time BETWEEN @startDate AND (DATEADD(MM,1,@startDate)-1)
) AS p
PIVOT
(
    MAX(p.Time)
    FOR p.State IN([Disabled],[Enabled])
) AS pvt