Help with this query please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with this query please.

Author

Topic

mr.modus
Starting Member

27 Posts

Posted - 2007-11-05 : 15:33:51

I posted this in the access forum too, but since I can move my access db to sql I will post it here too and hope noone considers it a double post.

Hello there,

I've been pulling my hair out with this and any help would be fantastic.

I have a query where I'm trying to get info out of three tables and I'm not sure how to Join and how to Group it.

My tables (with only the relevant fields) are:

tblPassengers
- PassengerID (int)
- BookingID (varchar(20))
- FlightNumber (varchar(20))
- FlightCompleted (bit)

tblBookings
- BookingDate (DateTime)
- BookingID (varchar(20))

tblFlights
- FlightNumber (varchar(20))
- FlightDate (DateTime)
- CityName (varchar(20))

Relations are:
tblPassengers.BookingID = tblBookings.BookingID
tblPassengers.FlightNumber = tblFlights.FlightNumber

What I want to do is get a count of all passengers (Count(tblPassengers.PassengerID)that have a completed flight (tblPassengers.FlightCompleted = 1) and that have flown within 30 months of their purchase (tblFlights.FlightDate < DateAdd("m",30,tblBookings.BookingDate)) within the last five years (tblFlights.FlightDate > 1/1/2003) grouped by CityName and Year.

So the result I would want would be something like:

Count Year City
453 2003 London
285 2003 Hockley Valley
138 2003 Another City
658 2004 London
260 2004 Hockley Valley

Any help would be awesome!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 18:54:10

[code]SELECT f.CityName, YEAR(f.FlightDate), COUNT(DISTINCT p.PassengerID)
FROM tblPassenger p
INNER JOIN tblBookings b ON p.BookingID = b.BookingID
INNER JOIN tblFlights f ON p.FlightNumber = f.FlightNumber
WHERE p.FlightCompleted = 1
AND f.FlightDate < DATEADD(minute, 30, b.BookingDate)
-- this does not really gives you last five years, you should use getdate() to determine
AND f.FlightDate > '20030101'
GROUP BY f.CityName, YEAR(f.FlightDate)[/code]

KH
[spoiler]Time is always against us[/spoiler]

mr.modus
Starting Member

27 Posts

Posted - 2007-11-06 : 09:17:36

That worked a charm! Thanks so much for showing me the way!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources