Rails - Jan 2008 - How to test if one range of dates overlaps another?

Hi all,

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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 16 Jan 2008, at 21:28, Neal L wrote:
>
> Hi all,
>
> 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.
>
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]

Fred

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Fred,

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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Solved my own problem... With a little inspiration from here:

http://forums.devshed.com/ms-sql-development-95/filtering-on-startdate-and-enddate-question-277324.html
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 16 Jan 2008, at 21:54, Neal L wrote:
>
> Fred,
>
> 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.

Fred
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---