Start and Stop times where condition true

[cheshire_smile]

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:

 
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:

 
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.

[boblarson]

See if this might be helpful:

http://www.baldyweb.com/OverLap.htm

[cheshire_smile]

See if this might be helpful:

http://www.baldyweb.com/OverLap.htm

Unfortunately this does not help me. In the example they already have a start and stop date time.

I have a list of time stamp and need to extract a start and stop time from the list.

[cheshire_smile]

anybody got any suggestions?