Overlapping dates

Database

Overlapping dates

Hello, I need help with overlapping dates. I have two tables, one with promotion and second with service plan. I need to pull service plan for corresponding promotion. My SQL pulls incorrect plan. Please let me know if you have suggestions on how to solve overlapping dates problem. Thanks a lot.

table 1

line_id promotion_cd start_dt end_dt

1 promo_1 3/7/2015 14:46 3/7/2015 17:23

2 promo_2 3/30/2015 22:27 3/30/2015 22:53

table 2

line_plan_id line_id plan_cd start_dt end_dt

11 1 plan_1 12/7/2013 3/7/2015

12 1 plan_2 3/7/2015 3/7/2015

13 1 plan_3 3/7/2015 3/8/2015

14 2 plan_4 11/9/2014 3/30/2015

15 2 plan_5 3/30/2015 3/30/2015

16 2 plan_6 3/30/2015 12/31/3000

My SQL returns:

line_id promotion_cd plan_cd

1 promo_1 plan_3

2 promo_2 plan_6

But need to see:

line_id promotion_cd plan_cd

1 promo_1 plan_2

2 promo_2 plan_5

SQL:

select lf.line_id,  promo_cd, plan_cd
from LINE_PROMOTION_V  lf                          

join line_plan_v lp
on lf.line_id=lp.line_id
and (cast(lf.START_DT as date), cast(lf.END_DT as date)) overlaps (lp.start_dt,coalesce(lp.end_DT,current_date+1))      

qualify row_number() over(partition by lf.line_id, lf.promo_cd order by  lp.line_plan_id desc)=1;
2 REPLIES
Enthusiast

Re: Overlapping dates

When using the OVERLAPS function, there are a couple of situations to keep in mind:

  1. A single point in time, i.e. the same date, does not constitute an overlap. There must be at least one second of time in common for TIME or one day when using DATE.
  2. Using a NULL as one of the parameters, the other DATE or TIME constitutes a single point in time versus a range.

Hope this helps!!

Enthusiast

Re: Overlapping dates

select lf.line_id,  promo_cd, plan_cd

from LINE_PROMOTION_V  lf                          

 

join line_plan_v lp

on lf.line_id=lp.line_id

and lf.START_DT <= lp.END_DT

and lf.END_DT >= LP.START_DT

qualify row_number() over(partition by lf.line_id, lf.promo_cd order by  lp.line_plan_id ASC)=1;

 

This should work