Combine two query results into one row?

[Stinney]

I have a table with data like this:

ArriveDate ArriveTime DepartDate DepartTime Duration
12/01/2012 08:00 12/02/2012 13:00 29:00:00
12/02/2012 07:00 12/02/2012 09:00 02:00:00
12/02/2012 08:00 12/02/2012 12:30 04:30:00
12/02/2012 12:30 12/02/2012 12:30 00:00:00
12/02/2012 12:30
12/02/2012 14:00

The last 2 entries don't have a departure date as they haven't happened yet.

I need to query this table so I get the results as:

Arrive/DepartDate Arrive/DepartTime TotalArrivals TotalDepartures AveDuration
12/02/2012 09:00 0 1 02:00:00
12/02/2012 12:30 1 2 02:15:00
12/02/2012 13:00 0 1 29:00:00
12/02/2012 14:00 1 0 00:00:00

I can't get this to work.

I've tried a union but the result I get is like this:

Arrive/DepartDate Arrive/DepartTime TotalArrivals TotalDepartures AveDuration
12/02/2012 09:00 0 1 02:00:00
12/02/2012 12:30 1 0 00:00:00
12/02/2012 12:30 0 2 02:15:00
12/02/2012 13:00 0 1 29:00:00
12/02/2012 14:00 1 0 00:00:00





- Stinney

I love learning and passing on knowledge. "Because knowing is half the battle".... ??GI JOOOOOE??

 

[PHV]

Why not posting your SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886