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