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.
| Author |
Topic |
|
welby
Starting Member
2 Posts |
 Posted - 2010-08-20 : 15:17:30
|
| Needs some help with a query. I am a novice when it comes to sql, so here goes.I have a table that logs alarms. When an alarm happens, a new row is created, and the start time and alarm type are registered. When the alarm clears, the stop time is written to the appropriate row.I need a sql query that I can run the will tell me how many alarms occurred during a time period. For example, how many alarms occurred during the time period of 7am -3pm? If they start before or after 7am it's ok. If they end before or after 3, it's ok. As long as the alarm was active during some period of that time.Any ideas? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-20 : 15:44:26
|
How do you want to handle alarms that haven't ended? I assume the you want to treat them "never ending" until they actually end?Here is one method that might work. If not, you might want to supply some DDL, DML and expected output that we can write queries against. Here is alink that might help with that:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxHere is the sample code:DECLARE @TimePeriod DATETIMESET @TimePeriod = <some date time>SELECT *FROM AlarmTableWHERE @TimePeriod BETWEEN StartDate AND COALESCE(EndDate, '9999-12-31') |
 |
|
|
welby
Starting Member
2 Posts |
Posted - 2010-08-23 : 15:51:11
|
| I was able to get the search I wanted. Your code gave me a nice jumping point. The funny thing is, it looks like the app had bee re-designed to the point that we will no longer need this search. Oh well...at least I found an answer.Thank you very much for your help and sample code. I appreciate it very much. |
|
|
|
|
|
|
Help with a time based sql query