PART2 - Conversion of crazy access query to mssql

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)

PART2 - Conversion of crazy access query to mssql

Author

Topic

mr.modus
Starting Member

27 Posts

Posted - 2007-09-12 : 15:56:52

I posted previously about a crazy access query which used saved queries. Here's the previous post: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88627[/url]. I got an answer almost right away and it really helped. However after using the answer for a while I'm realizing there's a bit of a gap in the way the system works. In brief, what I need to do is bring back flights for a specific city, day type (weekend/weekday) and time (am/pm) that has available weight and passenger space. The solution I was given previously works great, except it's only returning records for flights that meet these criteria AND have passengers already booked. If there are no passengers booked it doesn't return the flight even though it meets the city,day,time,space and weight requirements.

Main SQL query is this: (I'm using variables which are surrounded by # signs.)

SELECT
DISTINCT tbl_Flights.City_ID, tbl_Flights.Flight_Date, tbl_Flights.Flight_Time,
tbl_Flights.Flight_Number, tbl_Balloons.Balloon_Registration,
tbl_Balloons.Balloon_PassCapacity, ISNULL(LookupPassengerTotalByFlight.[Booked Passengers], 0) AS [Passengers_Booked],
tbl_Balloons.Balloon_PassCapacity - ISNULL(LookupPassengerTotalByFlight.[Booked Passengers], 0) AS [Spaces_Available],
tbl_Balloons.Balloon_LiftCapacity, ISNULL(LookupPassengerTotalByFlight.[Total Weight], 0) AS [Weight_Used],
tbl_Balloons.Balloon_LiftCapacity - ISNULL(LookupPassengerTotalByFlight.[Total Weight], 0) AS [Weight_Available],
tbl_Flights.Flight_Status

FROM tbl_Balloons

INNER
JOIN tbl_Flights
ON tbl_Flights.Balloon_ID = tbl_Balloons.Balloon_ID

LEFT OUTER
JOIN LookupPassengerTotalByFlight
ON LookupPassengerTotalByFlight.FlightNumber = tbl_Flights.Flight_Number

WHERE tbl_Flights.City_ID = #city_ID#
AND tbl_Flights.Flight_Time = 'AM'
AND tbl_Flights.Flight_Status = 'Scheduled'
AND tbl_Flights.Flight_Date >= #CreateODBCDateTime(today)#
AND DATEPART(WEEKDAY, tbl_Flights.Flight_Date) BETWEEN 2 AND 6
AND tbl_Balloons.Balloon_PassCapacity - ISNULL(LookupPassengerTotalByFlight.[Booked Passengers], 0) >= #totalPass#
AND tbl_Balloons.Balloon_LiftCapacity - ISNULL(LookupPassengerTotalByFlight.[Total Weight], 0) >= #totalWght#

ORDER BY tbl_Flights.Flight_Date, tbl_Flights.Flight_Time

The SQL View I'm using is called "LookupPassengerTotalByFlight" and here's the query for it:

CREATE VIEW dbo.LookupPassengerTotalByFlight
AS
SELECT FlightNumber, COUNT(*) AS [Booked Passengers],
SUM(Weight) AS [Total Weight]
FROM dbo.tbl_Passengers
GROUP BY FlightNumber

I'm sure that the problem is that the table dbo.tbl_Passengers only shows records for passengers that have booked flights, and since some flights don't have any booked passengers it's not returning those flights. I need this mess to not only return flights with passengers that meet the criteria but also flights with NO passengers that meet the criteria.

I know for some of you guys this will seem elementary and I promise you I've been trying to do it myself many time and ways before I come here and beg someone to help me. Sadly I'm just not smart enough.

Thanks so much for any help you can offer.
Sincerely,
mr.modus

mr.modus
Starting Member

27 Posts

Posted - 2007-09-13 : 11:48:32

Ok, I've figured it out. I just used another View to get all empty flights, then created a main view to UNION to the two views together. Works like a charm. Perhaps people didn't want to reply so that I'd figure it out myself? If so, thanks I guess.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources