Finding data that falls within a date range for a date range

The title of this is horribly confusing, but hopefully this is helpful for some people. The problem I was trying to solve is I have a report that shows where travelers are based on certain dates. For example a client requests a report to know where all of their travelers are that are traveling 9/1 – 9/2. Given that we know the departure and arrival dates of their trip this is pretty straightforward.

Given that their departure date is stored in depDateTime and their return date is stored in arvDateTime and startDate and endDate are timestamps then this will return all users who are traveling during 9/1 – 9/2

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#))

This works great for most trips, but what if the person departs on 8/31 and returns on 9/3? The current WHERE statement will not return them as a result. I fiddled with dozens of scenarios until I realized it’s pretty simple. For the user to not show with the current logic their trip needs to begin before the startDate and end after the endDate, so it required one additional OR statement

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#)
    OR (depDateTime < #startDate# AND arvDateTime > #endDate#))

Multiple insert statement without counter

I often have a need to write an INSERT statement that adds multiple rows to a database. Obviously you can run any ┬ánumber of inserts separately, but that’s innefficient, so I loop over a list with a counter to know when to end. Recently I learned that the counter is unnecessary and you can create a fake SELECT statement to avoid needing a counter

INSERT INTO Attendees (Schedule_ID, Attendee_ID, Meeting_ID, User_ID)
<cfloop list="#variables[eventlocation]#" index="i">
  SELECT #Schedule_ID#, #i#, #Meeting_ID#, 1
<!--- returns no rows --->
SELECT 0, 0, 0, 0
WHERE 1 = 0

For the sake of brevity I’ve avoided omitted the cfqueryparam’s that should always be used in queries.