Hi all. A while ago someone asked how to phrase a query for date intersections. I have been struggling all day to come up with what is probably very simple logic for date intersections. For instance, I need to find all the reserved rooms whose dates fall within the selected dates of a new reservation.
I came up with a way to do that. It's a bit more complicated than using the SQL between function because BETWEEN is inclusive, and if you think about it, it's fine if a new reservation falls on the departure day of an existing one. So given that startdate and enddate are SQL columns in a reservation database, and the literal dates are the new reservation start and end dates, I give you this where clause: (startdate >= "2012-1-11" AND startdate < "2012-1-14") OR (enddate > "2012-1-11" AND enddate <= "2012-1-14") This in effect allows new reservations to begin on the day another ends, or end on the day another begins. I don't know why this kind of logic comes so hard for me sometimes. I have to draw pictures to sort it out. At any rate, if I can prevent someone else from having to go through the mental hoops I did, then I feel I have contributed. Bob _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
