[josiahb]

I'm working on a new page for our company intranet and having some difficulty with an sql query, the page is meant to show all flight departures for a particular member of our planning team for the next 8 weeks. I've got the rest of it working its the 'next 8 weeks' bit which seems to be causing me problems. rather than showing the data as I'd like it shows all departures leaving in 8 weeks time or longer.

for instance it should be showing all departures up to the 3rd Sept at the moment but is instead showing all departures after the 3rd.

DROP TABLE IF EXISTS FDPlan;
CREATE TEMPORARY TABLE FDPlan
SELECT 
tourrefcode AS tourref, tourdeparturedate AS departuredate, toursequenceno AS sequenceno, itinerarylinefromairport AS airport, SUM(pax) AS booked
FROM
explorer.booking_record
INNER JOIN explorer.booking_linetype_record ON booking_linetype_record.bookingref = booking_record.bookingref
WHERE bookingstatus = 'F' AND itinerarylinetype = 'F' AND offset = 1 AND agentref <> 'LEAD' 
AND DATE_ADD(tourdeparturedate, INTERVAL -56 DAY) > CURDATE()
GROUP BY tourrefcode, tourdeparturedate, toursequenceno, itinerarylinefromairport;

SELECT FDPlan.tourref AS TourCode, FDPlan.DepartureDate, DM_Holidays.TourName, 
flight_availability_record.fromairport, flight_availability_record.toairport, flight_availability_record.flightno, 
flight_availability_record.depttime AS DepTime, flight_availability_record.arrivaltime,
FDPlan.booked, LE_Departures.LeaderName, LE_Departures.LeaderUID, DM_Planning.planner
FROM explorer.itinerary_linetype_record 
INNER JOIN explorer.flight_availability_record ON (itinerary_linetype_record.itinerarylinefromairport = flight_availability_record.fromairport) AND (itinerary_linetype_record.itinerarylinetoairport = flight_availability_record.toairport) AND (itinerary_linetype_record.itinerarylinerelief = flight_availability_record.reliefcode)
INNER JOIN explorer.itinerary_date_record ON itinerary_date_record.tourref = itinerary_linetype_record.tourref AND itinerary_date_record.departuredate = itinerary_linetype_record.departuredate AND itinerary_date_record.sequenceno = itinerary_linetype_record.sequenceno
INNER JOIN FDPlan ON FDPlan.tourref = itinerary_linetype_record.tourref AND FDPlan.departuredate = itinerary_linetype_record.departuredate AND FDPlan.sequenceno = itinerary_linetype_record.sequenceno AND FDPlan.airport = flight_availability_record.fromairport
INNER JOIN explorer.airport ON flight_availability_record.fromairport = airport.code
INNER JOIN DM_Tours ON DM_Tours.TourCode = itinerary_date_record.tourref
INNER JOIN DM_Holidays ON DM_Tours.PrimaryTourCode = DM_Holidays.PrimaryTourCode 
LEFT JOIN explorer.itinerary_closed_reasons ON (itinerary_closed_reasons.sequenceno = itinerary_date_record.sequenceno) AND (itinerary_closed_reasons.departuredate = itinerary_date_record.departuredate) AND (itinerary_closed_reasons.tourref = itinerary_date_record.tourref)
LEFT JOIN leaders.LE_Departures ON LE_Departures.DepartureDate = itinerary_date_record.departuredate AND LE_Departures.TourCode = itinerary_date_record.tourref 
LEFT JOIN DM_Planning ON FDPlan.tourref = DM_Planning.Tourcode
WHERE DATE_ADD(itinerary_linetype_record.departuredate, INTERVAL itinerary_linetype_record.offset - 1 DAY) = flight_availability_record.flightdate AND (airport.country ="GB" OR airport.code = 'BHX') AND airport.code <> 'GCI'
AND (itinerary_closed_reasons.closedoutreason IS NULL OR itinerary_closed_reasons.closedoutreason = 0) AND Planner='{StaffSearchID}'
ORDER BY TourCode, FDPlan.DepartureDate;

Now, I'm fairly certain someone will very rapidly point out the problem which mind blind eyes can't see, but then I am a n00b at all this stuff

[josiahb]

damn... and there was me thinking the answer would be here when I got in this morning

AND DATE_ADD(tourdeparturedate, INTERVAL -56 DAY) > CURDATE()

Its obviously this statement which isn't doing what I want, but my knowledge isn't good enough to know how to fix it.

[josiahb]

Just actually read the above function properly.... nothing like a good nights sleep and a cup of tea to help you realise your being a dumbass.

Worth noting that I didn't actually write this in the first place, never blindly accept the stuff your boss hands you even if he is the accomplished dev and you aren't!

[dineeshd]

use the between operator to solve this...

AND tourdeparturedate BETWEEN CURDATE() AND DATE_ADD(tourdeparturedate, INTERVAL 56 DAY)

[josiahb]

Thanks dineeshd, thats a far more elegnat solution than I managed to come up with!