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.

 

[spikepl]

Why is
2-----------6/19/2011 00:12-Null

meaning, how does it differ from

4----2----6/19/2011-00:04---0----0

also
where up and dn <> 0. means
up=1 AND dn=1 or does it mean
up=1 OR dn=1 ?

 

[cheshire_smile]

Why is
2-----------6/19/2011 00:12-Null

meaning, how does it differ from

4----2----6/19/2011-00:04---0----0

Because there is no stop time, or no value where up AND dn <> 0 after wards.

also
where up and dn <> 0. means
up=1 AND dn=1 or does it mean
up=1 OR dn=1 ?

It's Up AND Dn <> 0.
I only used 1s in the example, but the values can be more than 1.

 

[spikepl]

Because there is no stop time, or no value where up AND dn <> 0 after wards.

I don't get it.

What is the difference between

4----2----6/19/2011-00:04---0----0

and

12---2----6/19/2011-00:12---0----0

again? For how exactly do you understand "afterwards"? 3 rows with 0 0 ?

 

[cheshire_smile]

The sample I provided is for only one terminal_ID, and is from 00:01 to 00:14 on the 19th for Jun. (There will be other terminals as well).

Notice that the records are ordered by time_stamp

AT 00:04 the up and dn went from 1 on the previous record to 0
AT 00:06 the up and dn went from 0 on the previous record to 1

AT 00:12 The up and dn went from 1 on the previous record to 0
But there are no records of it changing back to a 1 after 00:12
so the stop time is null

 

[spikepl]

OK that's a nasty condition. I'll have a think about this, but don't hold your breath

 

[cheshire_smile]

If it helps, this is what I came up with.
but this is too combersome, because it runs each record times each record. So if i'm running it on 100K records, then it's processing it a billion times.
I've tried indexing all the fields, I've also split the Terminal_Name from the table and given it a Terminal_ID.

SELECT 
 Outage_Times.Terminal_Name,
 Outage_Times.Down_Time,
 Outage_Times.Up_Time,
 (([Outage_Times].[Up_Time]-[Outage_Times].[Down_Time])*1440) AS Outage_Duration
FROM (
  SELECT UpDn_Analysis.Terminal_Name,
  Min(UpDn_Analysis.[Date/Time]) AS Down_Time,
  UpDn_Analysis.Next_Up_Time AS Up_Time
  
  FROM (
   SELECT
   [Temp_Table].Terminal_Name,
   [Temp_Table].[Date/Time],
   (SELECT Min(NxtUp.[Date/Time])
                  FROM [Temp_Table] NxtUp
                  WHERE NxtUp.[Terminal_Name] = [Temp_Table].[Terminal_Name]
                  AND NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
                  AND NxtUP.[Dn Total] <> 0
                  AND NxtUP.[Up Total] <> 0) AS Next_Up_Time,
   (SELECT Max(PrevDn.[Date/Time])
                  FROM [Temp_Table] PrevDn
                  WHERE PrevDn.[Terminal_Name] = [Temp_Table].[Terminal_Name]
                  AND PrevDn.[Date/Time] <= [Temp_Table].[Date/Time]
                  AND PrevDn.[Dn Total] = 0
                  AND PrevDn.[Up Total] = 0) AS Previous_Dn_Time
   FROM Temp_Table) AS UpDn_Analysis
   WHERE (((UpDn_Analysis.[Date/Time])=[Previous_Dn_Time]))
   GROUP BY UpDn_Analysis.Terminal_Name, UpDn_Analysis.Next_Up_Time)  AS Outage_Times;

 

[spikepl]

Ok I made some VBa that seems to run. Open From1 and click the one and only button.