Hard query statement help

ExtremeGuy

I am looking for some help in the formation of a MySQL query.

I have a site that stores reservation dates (ie. arrive: 2004-08-14, depart 2004-08-21). What I am trying to do is create a calendar to show what dates have been reserved already.

Right now, my SQL query statement is (assuming the month that I want is August 2004): SELECT dayofmonth(rv_arrive) as aDate, dayofmonth(rv_depart) as dDate, (TO_DAYS(rv_depart) - TO_DAYS(rv_arrive)) as stay FROM rt_rv WHERE year(rv_arrive)=2004 AND month (rv_arrive)=08

At first this looks fine, however, if I have a reservation that starts in the month of July (07) and ends in August (08), that will not show up because my query statement is just looking for dates between Aug 1 and Aug 31 ONLY. This gets even worse if the arrival day is before August 1 and the departure day is after Aug 31.

So my question is: How do I write a query to capture ALL of the reservations that are to be shown in my month of August? Should I be storing my dates differently?

Thanks!

Roger_Ramjet

Originally posted by ExtremeGuy
SELECT dayofmonth(rv_arrive) as aDate, dayofmonth(rv_depart) as dDate, (TO_DAYS(rv_depart) - TO_DAYS(rv_arrive)) as stay
FROM rt_rv
WHERE year(rv_arrive)= 2004 AND month(rv_arrive)<09 AND month(rv_depart)>7

If they arrive after Aug 31 then they are outside August, and if they depart before Aug 1 then they are outside August. So someone who arrives before September and departs after July is in August for at least a part of their stay.

Simple logic does it every time.🆒

ExtremeGuy

Originally posted by Roger Ramjet
If they arrive after Aug 31 then they are outside August, and if they depart before Aug 1 then they are outside August. So someone who arrives before September and departs after July is in August for at least a part of their stay

I am looking to capture the people arriving before August 1st and departing in August.

If someone arrives BEFORE August 1st AND departs AFTER August 31st, then I still need my code to find it so that I can mark the whole month of August as being "rented"

See, the logic isn't so simple, which has me perplexed. 😕

Roger_Ramjet

Sorry, I missunderstood. I thought you wanted anyone who was there for any part of August. If you only want those who arrive before Aug 1 then simply change month(rv_arrive)<09 to 08 instead.

ExtremeGuy

Originally posted by Roger Ramjet
Sorry, I missunderstood. I thought you wanted anyone who was there for any part of August. If you only want those who arrive before Aug 1 then simply change month(rv_arrive)<09 to 08 instead.

No, you are correct, I am ONLY looking for dates that are in August. SO, if someone's arrival date is July 29th and their departure date is Aug 4th, my query WILL NOT find them. I am looking for help in finding ALL reservations INCLUDING ones like this.

Roger_Ramjet

Look. If you want anyone who was there for ANY part of August then use my first query. If you want those who were there for any part of August BUT who arrived before the 1 Aug then use the second query. That is it.

ExtremeGuy

Originally posted by Roger Ramjet
Look. If you want anyone who was there for ANY part of August then use my first query. If you want those who were there for any part of August BUT who arrived before the 1 Aug then use the second query. That is it.

I AM a bonehead! :rolleyes: I have been having a brain cramp and couldn't see this for myself - thanks for pointing this out to me!

Here is the new query that I am using:
SELECT rv_key, dayofmonth(rv_arrive) as aDate, dayofmonth(rv_depart) as dDate, (TO_DAYS(rv_depart) - TO_DAYS(rv_arrive)) as stay FROM rt_rv WHERE rv_status=1 AND rv_pi_key=1001 AND year(rv_arrive)=2004 AND ((month (rv_arrive)<=02) AND (month (rv_depart)>=02))
(I still need to work on the YEAR part of this, ie. If the reservation started in December and ended in January)