I need help with developing a query, or a VBA script to get start and stop times where a condition is met. I have a table called Temp_Table. Sample data:
Code:
ID-Terminal_ID-Time_Stamp--Dn---Up
1----2----6/19/2011-00:01---1----1
2----2----6/19/2011-00:02---1----1
3----2----6/19/2011-00:03---1----1
4----2----6/19/2011-00:04---0----0
5----2----6/19/2011-00:05---0----0
6----2----6/19/2011-00:06---1----1
7----2----6/19/2011-00:07---1----1
8----2----6/19/2011-00:08---0----0
9----2----6/19/2011-00:09---1----1
10---2----6/19/2011-00:10---1----1
11---2----6/19/2011-00:11---1----1
12---2----6/19/2011-00:12---0----0
13---2----6/19/2011-00:13---0----0
14---2----6/19/2011-00:14---0----0
I need the start time where up and dn = 0 and the stop time where up and dn <> 0.
The result should look like this:
Code:
Terminal_ID------Start----------Stop
2-----------6/19/2011 00:04-6/19/2011 00:06
2-----------6/19/2011 00:08-6/19/2011 00:09
2-----------6/19/2011 00:12-Null
and it needs to be efficient as it will be running on 100K records. I have a query that works but it uses corelated queries in the select statement and it takes way too long.
I've been working on this for over a week now and I'm not getting anywhere. any help would be appreaciated.