Searching for data transitions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Searching for data transitions

I have data that includes a time_date field, and a large number of data fields. One field in particular is a status number, which can be 0, 1, or 2. The number 2 is "fault" mode, and persists until a user resets the hardware. In my query, I then want to count the number of faults, Therefore, since the fault stat persists, I cannot just count the number of rows with a "2". I neeed to count the number of transitions from 0 or 1 to 2. Here is a brief sample, trimmed for space reasons. The query should return "3", for the start of faults at 10:47:08, 11:51:01, and 12:15:57. In addition, I would like either the same query or a separate one that would return the timestamp of the last fault, in this sample it would return 2005-12-07 12:15:57. Thanks in advance for your help in this! Chuck Date_Time SystemWarningFaultState
2005-12-07 10:39:02 0
2005-12-07 10:40:16 0
2005-12-07 10:41:23 0
2005-12-07 10:42:01 0
2005-12-07 10:43:02 0
2005-12-07 10:44:17 0
2005-12-07 10:45:23 0
2005-12-07 10:46:03 0
2005-12-07 10:47:08 2
2005-12-07 10:48:03 2
2005-12-07 10:49:15 2
2005-12-07 10:50:14 2
2005-12-07 10:51:02 2
2005-12-07 10:52:37 0
2005-12-07 10:53:01 0
2005-12-07 10:54:06 0
2005-12-07 11:44:01 0
2005-12-07 11:45:02 0
2005-12-07 11:46:11 1
2005-12-07 11:47:01 1
2005-12-07 11:48:24 1
2005-12-07 11:49:06 1
2005-12-07 11:50:06 1
2005-12-07 11:51:01 2
2005-12-07 11:52:03 2
2005-12-07 11:53:12 2
2005-12-07 11:54:01 2
2005-12-07 11:55:20 2
2005-12-07 11:56:01 2
2005-12-07 11:57:19 2
2005-12-07 11:58:04 2
2005-12-07 11:59:01 2
2005-12-07 12:00:03 0
2005-12-07 12:01:12 0
2005-12-07 12:02:13 0
2005-12-07 12:03:11 0
2005-12-07 12:12:16 0
2005-12-07 12:13:16 0
2005-12-07 12:15:57 2
2005-12-07 12:17:44 2
2005-12-07 12:18:02 2
2005-12-07 12:19:53 2
2005-12-07 12:20:35 2
2005-12-07 12:21:03 2
2005-12-07 12:23:37 2
2005-12-07 12:25:34 2
2005-12-07 12:26:10 2
2005-12-07 12:27:16 2
2005-12-07 12:28:37 2
2005-12-07 12:29:01 2
2005-12-07 12:30:04 2
2005-12-07 12:33:24 2
2005-12-07 12:34:36 2
2005-12-07 12:35:58 2
2005-12-07 12:36:01 2
2005-12-07 12:37:10 2
2005-12-07 12:38:00 2
2005-12-07 12:39:12 2
2005-12-07 12:40:30 2
2005-12-07 12:41:30 0
2005-12-07 12:42:05 0
2005-12-07 12:43:20 0
2005-12-07 13:02:03 0
You want something like this. If there isn’t an identity column on the table, import the table to one and run your calculation or come up with some screwy derived table logic to do the smae thing. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @djl_test TABLE(ident INT IDENTITY(1,1) PRIMARY KEY, date_col DATETIME, status INT)<br /><br />INSERT @djl_test(date_col, status)<br />SELECT ‘2005-12-07 10:39:02′,’0’ UNION ALL <br />SELECT ‘2005-12-07 10:40:16′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:41:23′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:42:01′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:43:02′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:44:17′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:45:23′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:46:03′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:47:08′,’2’ UNION ALL<br />SELECT ‘2005-12-07 10:48:03′,’2’ UNION ALL<br />SELECT ‘2005-12-07 10:49:15′,’2’ UNION ALL<br />SELECT ‘2005-12-07 10:50:14′,’2’ UNION ALL<br />SELECT ‘2005-12-07 10:51:02′,’2’ UNION ALL<br />SELECT ‘2005-12-07 10:52:37′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:53:01′,’0’ UNION ALL<br />SELECT ‘2005-12-07 10:54:06′,’0’ UNION ALL<br />SELECT ‘2005-12-07 11:44:01′,’0’ UNION ALL<br />SELECT ‘2005-12-07 11:45:02′,’0’ UNION ALL<br />SELECT ‘2005-12-07 11:46:11′,’1’ UNION ALL<br />SELECT ‘2005-12-07 11:47:01′,’1’ UNION ALL<br />SELECT ‘2005-12-07 11:48:24′,’1’ UNION ALL<br />SELECT ‘2005-12-07 11:49:06′,’1’ UNION ALL<br />SELECT ‘2005-12-07 11:50:06′,’1’ UNION ALL<br />SELECT ‘2005-12-07 11:51:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:52:03′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:53:12′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:54:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:55:20′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:56:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:57:19′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:58:04′,’2’ UNION ALL<br />SELECT ‘2005-12-07 11:59:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:00:03′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:01:12′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:02:13′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:03:11′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:12:16′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:13:16′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:15:57′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:17:44′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:18:02′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:19:53′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:20:35′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:21:03′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:23:37′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:25:34′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:26:10′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:27:16′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:28:37′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:29:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:30:04′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:33:24′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:34:36′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:35:58′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:36:01′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:37:10′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:38:00′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:39:12′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:40:30′,’2’ UNION ALL<br />SELECT ‘2005-12-07 12:41:30′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:42:05′,’0’ UNION ALL<br />SELECT ‘2005-12-07 12:43:20′,’0’ UNION ALL<br />SELECT ‘2005-12-07 13:02:03’,’0′<br /><br />–SELECT * FROM @djl_test<br /><br />SELECT COUNT(dt2.date_col), MAX(dt2.date_col) AS fault_count<br />FROM<br />@djl_test dt1<br />INNER JOIN @djl_test dt2 on dt1.ident = dt2.ident – 1<br />AND dt1.status IN (0,1)<br />AND dt2.status = 2<br /><br /></font id="code"></pre id="code"><br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Thanks. This is a great start. I do not have an identity column, that sure would help. The timestamp is asynchronous, so I cannot use that. Perhaps a temporary table is the only way to go. I’ll ask the DB owner if he can add an identity column. I think in non SQL-server DB’s (such as access) you can refer to ROWNUM of your result set, but I don’t think SQL server has that feature. Even if I order by Date_Time, I still need to refer to that prior entry, and the identity column is probably the best way to do that. ANyone else know of a way to refer to the prior entry in the result set without identity? Thansk again.
SQL Server doesn’t have row number in the 2000 version. The other way to do it would involve complex derived tables. If there aren’t going to be tons of records involved, I would just do it with the temp table. The best solution would be to change the design to either: 1. Have an ID.
2. Have the procedures that right the events also write to an aggregation or status table as needed to make the reporting easier. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
OK, I think a temp table will work fine for this. What about part 2, getting the date_time of the last fault? Thanks again
Chuck
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Did you see what the MAX date was returning?<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Oops. So fixated on the count portion. My bad! Thanks again! Chuck
]]>