Simple SQL Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple SQL Question

Author

Topic

Tonkadan
Starting Member

1 Post

Posted - 2008-06-10 : 11:07:56

Hi,

I'm pretty new to SQL, i've been able to do everything i wanted until now and i have no clue if it's possible in SQL.

I have a Device that may be ON or OFF. Everytimes that device goes ON or OFF i'm updating a Table called Device_Status. Here's the structure of my table:

id Device Status TimeStamp
---------------------------------------------------------------------
001 MyDevice1 MyDevice1 is OFF 2008-06-10 10:36:47
002 MyDevice1 MyDevice1 is ON 2008-06-10 10:37:42
003 MyDevice1 MyDevice1 is OFF 2008-06-10 10:39:47

And i have another table that is logging Alarms. Each time a alarm is generated, the alarm is logged in the table Alarms with the following structure:

id Alarm AlarmMessage GenerationTime
---------------------------------------------------------------------
001 Door_Open blablablablabla 2008-06-10 10:32:47
002 Door Unlocked blablablablabla 2008-06-10 10:38:12
003 Key accepted blablablablabla 2008-06-10 10:45:38

Now i want to select all alarms from the table Alarm but only if the alarm was generated when the Device1 was ON. I really dont know how to do that because alarms are not generated in the exact time that the status of the device is updated.

You may note that i can't change the way the status is logged.

I know it's probabley easy but i can't figure how to do that...

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:17:48

[code];
With Device_CTE (RowNo,id ,Device, Status, TimeStamp) AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY Device ORDER BY TimeStamp) AS RowNo,
*
FROM Device_Status)

SELECT a.* FROM Alarms a
INNER JOIN
(SELECT c1.Device,c1.TimeStamp AS StartTime,c2.TimeStamp AS EndTime
FROM Device_CTE c1
LEFT JOIN Device_CTE c2
ON c1.Device=c2.Device
AND c1.RowNo=c2.RowNo-1
AND c1.Status LIKE '%ON'
AND c2.Status LIKE '%OFF')tmp
ON a.GenerationTime>=tmp.StartTime
AND (a.GenerationTime<=tmp.EndDate OR tmp.EndDate IS NULL)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources