Time differences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I have a table with the following data in:

Flights Chute Open Close
BA121 1b 05:00 07:00
BA123 1b 09:15 12:15
BA432 1b 13:20 15:30
BA341 1b 17:30 20:10

What I need to be able to do is get the time difference between the first
close time and next open time ie..

ba121 closes at 07:00 ba123 opens at 09:15 so time avail between 2:15

the ouput I need is as follows:

Flights Chute Open Close TimeAvail
BA121 1b 05:00 07:00 02:15
BA123 1b 09:15 12:15 01:05
BA432 1b 13:20 15:30 02:00
BA341 1b 17:30 20:10

any ideas would be mucho appreciated.....
 
Hi,


SELECT a.Chute, a.Close, b.Open, b.Open-a.Close As TimeAvail
FROM (myTable As a INNER JOIN myTable As b
ON a.Chute=b.Chute and a.Close < b.Open)
INNER JOIN myTable As c
ON a.Chute=c.Chute AND a.Close < c.Open

GROUP BY a.Chute, a.Close, b.Open

HAVING b.Open = MIN( c.Open)



Clearly, b.Open occurs after a.Close. Same thing for c.Open > a.Close. The
problem is that it would list all occurrence becoming AFTER, not just the
single NEXT one. So, for each group, we only keep those b.Open that matches
the minimum c.Open to only keep the NEXT ONE (for a given chute, a.close,
and b.open) following a.Close. That is done through the HAVING clause.



Hoping it may help,
Vanderghast, Access MVP
 
Allen

Thanks for the pointer but the browser was having problems loading the
page.....
will try later....

Cheers for the feedback though!!!
 
Back
Top