Jump to content

Selecting Records Within A Date Range


murfitUK

Recommended Posts

This is using mysql.A table contains records with a start date and an end date (used to indicate when a service was provided). Each record has a start date but may have a null end date if the service is ongoing. The dates are stored in the standard 2009-06-20 format.What I'm trying to do is produce a query to pick out records that were provided with a service between two dates eg 2009-05-01 to 2009-05-31, but it appears to be more complicated than I thought.The fields are called `start` and `end`, the dates for the query are called $rsd (report start date) and $red (report end date).Suppose a record has start and end dates of 2009-06-20 to 2009-08-13. Running a report for 2009-05-01 to 2009-05-31 should not pick out this record. Using dates 2009-06-01 to 2009-06-30 WILL pick it out, as will 2009-08-01 to 2009-08-31 etc. Running a report for 2009-09-01 to 2009-09-30 should not pick it out.It gets more complicated when the report dates fall completely within the record's dates eg running a report for 2009-07-08 to 2009-07-14, and also if the record's end date is null.I've been trying to write a select query using where... and/or... for every conceivable scenario but I'm sure there must be an easier way - perhaps by excluding records that don't fall within the dates, rather than trying to select records that do match?Can anyone help me? Perhaps someone has already written such a query?

Link to comment
Share on other sites

Either way it's going to be a series of conditions. I've written a lot of date-range reports in my time, and I usually just get out a piece of paper and draw it out. I write out a timeline where I say that the ends of this line are the start and end dates for the report, and then I draw out all of the timelines for the records that should match. So one record that should match is where the start date is before the rsd, but the end date is after the rsd and before the red. Another record that should match is where the end date is after the red, but the start is after the rsd and before the red. Another record is where the start date is before the rsd, and the end date is after the red. The last type of record is there the start date is after the rsd and the end date is before the red.So when I draw those out it ends up looking like this:

		  rsd						red		   |--------------------------|1. start		end	 |-----------|	 2.						   start		  end							   |-------------|							   3. start									end	 |---------------------------------------|	 4.			  start		 end				  |------------|

So you translate that into logical conditions, and get something like this:

(start < rsd AND end > rsd) OR(start < red AND end > red) OR(start < rsd AND end > red) OR(start > rsd AND end < red)

There's one of those you can remove though, the #3 case is also covered by #1. If the start is less than rsd and the end is greater than red, the end will also be greater than rsd. So we can leave out #3. That leaves this:

(start < rsd AND end > rsd) OR(start < red AND end > red) OR(start > rsd AND end < red)

So you can select every record where any part of the record falls between the start and end dates of the report using only 3 conditions. You might even be able to minimize that further.

Link to comment
Share on other sites

Thanks for that - you made it sound so simple. Actually, I did try drawing a timeline but I did it a different way. I drew a line for the report start and end dates, and then drew in all the possible records. Think I ended up with about 10 different lines before throwing the bit paper in the bin!The only thing for me to figure out now is how to cope with records that don't yet have an end date (eg where the service is ongoing). Should be fairly straightforward.Thanks again.

Link to comment
Share on other sites

  • 3 weeks later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...