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!
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
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
