How to match intersection of dates between two periods.

Problem: Find  a condition that selects date ranges that have an intersection with a given range.

Example:
A subscription has a start-date and and an end-date. 
We want to know what subscriptions had at least one active day in a given month.
 

            +--> mth_strt_dt: Begin of Month
            :                     +--> mtd_end_dt: End of Month
            :                     :
            :                     :                   
            |<====== Month ======>|
   |<--->|  :                     :                No
     |<-------->|                 :                Yes
     |<-------------------------------------->|    Yes
            :     |<----->|       :                Yes
            :         |<--------------------->|    Yes
            :                     :    |<---->|    No

The Condition is:
                        Begin_Date <= EoM
                    and End_Date   >= BoM

 

Solution:

Select subscriptions where
subscription-end-date >= Begin-of-Month
and
subscription-begin-date <= End-of-Month



No comments:

Post a Comment

Followers (Guests)