I have a situation with a recurring billing application where I need
to test if one range of dates "overlaps" another range of dates. Does
anyone know a rails find or mysql select statement that will test for
this? Here are the details:
* Recurringcharge items have a start_date and an end_date that
represent the period of time that the charges should occur.
* Statement items have begin_on and end_on properties that represent
the period of time that is being billed for.
In order to create a statements, I need to see which recurringcharges
occur within the statement period.
I've been racking my brains, but can't seem to come up with the
elegant way to do this. Any ideas?
What's the input ? if the input is a given statement item (ie a begin & end time) then it's just
RecurringCharge.find :all, :conditions => ['start_date <= ? AND end_date >= ?', end_on, begin_on]
Thanks for the quick reply, but it's not quite that simple. A
recurring charge has a defined start and end date. If the statement
is being generated within the start and end dates, then the simple
test you suggested would work just fine. The tricky part is when a
recurring charge item only occurs during *part* of a statement's
period.
For example, let's say you have a recurring charge called Rent where
start_on = 1/1/2008 and end_on = 12/31/2008. You want to generate a
statement that covers the period from 12/15/2008 through 1/14/2009.
The Rent recurring charge does occur within the statement period, but
the charge's end_on (12/31/08) is before the end_on date of the
statement ( 1/14/2009).
So what I really need to test for is overlap. Does this make sense?
Thanks for the quick reply, but it's not quite that simple. A
recurring charge has a defined start and end date. If the statement
is being generated within the start and end dates, then the simple
test you suggested would work just fine. The tricky part is when a
recurring charge item only occurs during *part* of a statement's
period.
For example, let's say you have a recurring charge called Rent where
start_on = 1/1/2008 and end_on = 12/31/2008. You want to generate a
statement that covers the period from 12/15/2008 through 1/14/2009.
The Rent recurring charge does occur within the statement period, but
the charge's end_on (12/31/08) is before the end_on date of the
statement ( 1/14/2009).
So what I really need to test for is overlap. Does this make sense?
Which is exactly what the code I showed does
The condition i suggested is [start_date <= ? AND end_date >= ?',
end_on, begin_on]
the rent's start (1/1/2008) is certainly before the period end
(1/14/2009) and the rent's end(12/31/2008) is certainly >= the period
start (12/15/2008)
To rephrase in english, a charge should be included if it starts
before the end of the period and finished after its start.
The condition i suggested is [start_date <= ? AND end_date >= ?',