Complex Query

[ShadowTech]

Hope someone can help, this has really stumped me in terms of the best way to get the data with the least code (or at least most effecient code).

I have two tables

Flights
FlightID - UniqueID primary key
FlightName - Text
FlightDate - Date

PassengerFlight s
Passenger ID - UniqueID primary key
Flight ID - UniqueID foriegn key
Last Name - Text
First Name - Text
Seat - Text

I need a query that can group on Last Name & First Name and for each group I need to see a count of that passenger's total flights in the last year, FlightDate of the passengers last flight, FlightName of the last flight, and the Seat they were in on their last flight.

Any ideas? Thank-you

[BHTanna]

Last flight details should be of last year only or latest from data???

If you want from last year only then try following Query:

select P.[Last name],p.[First Name],d.[Total flights],[Last Flight Date],f.[flightName],p.seat

from PassengerFlight s P inner join
(
select [Passenger ID],count(*) as [Total flights],max(FlightDate ) as [Last Flight Date]
from PassengerFlight s p inner join Flights f on p.FlightID=f.Fl ightID
where year(FlightDate )=2007
group by [Passenger ID]
) d on d.[Passenger ID]=p.[Passenger ID]
inner join Flights f on f.flightID=p.fl ightID
and f.flightDate=d.[Last Flight Date]

[ShadowTech]

Last flight details should be of last year only or latest from data???

If you want from last year only then try following Query:

select P.[Last name],p.[First Name],d.[Total flights],[Last Flight Date],f.[flightName],p.seat

from PassengerFlight s P inner join
(
select [Passenger ID],count(*) as [Total flights],max(FlightDate ) as [Last Flight Date]
from PassengerFlight s p inner join Flights f on p.FlightID=f.Fl ightID
where year(FlightDate )=2007
group by [Passenger ID]
) d on d.[Passenger ID]=p.[Passenger ID]
inner join Flights f on f.flightID=p.fl ightID
and f.flightDate=d.[Last Flight Date]

That works well, I was hoping I could do it with one subquery join.
I was actually looking for a rolling year so I jsut changed the where clause on the date to this.

select [Passenger ID],count(*) as [Total flights],max(FlightDate) as [Last Flight Date]
from PassengerFlights p inner join Flights f on p.FlightID=f.FlightID
where FlightDate >= dateadd(day,-365,GetDate())
group by [Passenger ID]
) d on d.[Passenger ID]=p.[Passenger ID]
inner join Flights f on f.flightID=p.flightID
and f.flightDate=d.[Last Flight Date]

Thanks for the help!