special query please - 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 2008 Forums

Transact-SQL (2008)

special query please

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-08-01 : 10:53:38

Hi,

I could use some help converting a table into a query. We have a system at home that beeps 3 times if the doorbell goes, and beeps one time if someone passes the infrared alarm.

I already have a query that converts this analog signals to this form:

tblAlarmDoor
timestamp---------------value
2009-08-03 16:04:10.810 1
2009-08-03 16:07:45.013 1
2009-08-03 16:11:03.013 1
2009-08-03 16:22:20.413 1
2009-08-03 16:22:21.410 1
2009-08-03 16:22:22.410 1
2009-08-03 16:22:33.610 1
2009-08-03 16:25:45.410 1
2009-08-03 16:25:46.410 1
2009-08-03 16:25:47.410 1
2009-08-03 16:29:39.210 1
2009-08-03 16:29:40.210 1
2009-08-03 16:29:41.210 1
2009-08-03 16:33:51.407 1

What I want is to split the doorbell from the ir-alarm, so that I get maybe something like this:

timestamp---------------value
2009-08-03 16:04:10.810 1
2009-08-03 16:07:45.013 1
2009-08-03 16:11:03.013 1
2009-08-03 16:22:20.413 2
2009-08-03 16:22:33.610 1
2009-08-03 16:25:45.410 2
2009-08-03 16:29:39.210 2
2009-08-03 16:33:51.407 1

or, even better:

timestamp---------------alarm-doorbell
2009-08-03 16:04:10.810 1 0
2009-08-03 16:07:45.013 1 0
2009-08-03 16:11:03.013 1 0
2009-08-03 16:22:20.413 0 1
2009-08-03 16:22:33.610 1 0
2009-08-03 16:25:45.410 0 1
2009-08-03 16:29:39.210 0 1
2009-08-03 16:33:51.407 1 0

If anyone has some suggestions or help please let me know!

Thanks in advance, Joris

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 11:02:15

how far apart will 3 signals for alarm comes in ?

KH
[spoiler]Time is always against us[/spoiler]