SQL merge with interval dates

user_14411109 0
Viewed 78

Could someone please help figure out the right way to merge the below datasets ? I am struggling to merge df with interval but keep both the right dates for id=1 and not throw out id=3. I have tried multiple combinations of left joins but slicing depends on the order and neither of them works. Please note I need this to be scalable on the large dataset. This is presto db. I also need to merge interval with df somehow to make sure that only ids in df are present.

Code:


with interval as (
  select 2000 as startd, 2005 as endd,1 as id
  union all
  select 2003 as startd, 2005 as endd,2 as id
  union all
  select 2000 as startd, 2006 as endd,4 as id
),

all_dates as (
  select 2000 as date
  union all
  select 2001 as date
  union all
  select 2002 as date
  union all
  select 2003 as date
  union all
  select 2004 as date
  union all
  select 2005 as date
  union all  
  select 2006 as date
),

df as (
  select 2003 as date, 1 as id
  union all
  select 2004 as date, 1 as id
  union all
  select 2005 as date, 1 as id
  union all 
  select 2003 as date, 2 as id
  union all
  select 2004 as date, 2 as id
  union all
  select 2005 as date, 2 as id
  union all 
  select 2000 as date, 3 as id
)


select 
  all_dates.date,
  df.id
from 
  all_dates
left join 
  df 
on 
  all_dates.date = df.date
left join 
  interval
on 
  all_dates.date >= interval.startd
  and all_dates.date <= interval.endd
  and interval.id = df.id

Current output:

+------+----+
| date | id |
+------+----+
| 2006 |    |
+------+----+
| 2002 |    |
+------+----+
| 2001 |    |
+------+----+
| 2003 | 1  |
+------+----+
| 2004 | 1  |
+------+----+
| 2005 | 1  |
+------+----+
| 2004 | 2  |
+------+----+
| 2003 | 2  |
+------+----+
| 2005 | 2  |
+------+----+
| 2000 | 3  |
+------+----+

Desired output:

+------+----+
| date | id |
+------+----+
| 2006 |    |
+------+----+
| 2000 | 1  |
+------+----+
| 2002 | 1  |
+------+----+
| 2001 | 1  |
+------+----+
| 2003 | 1  |
+------+----+
| 2004 | 1  |
+------+----+
| 2005 | 1  |
+------+----+
| 2004 | 2  |
+------+----+
| 2003 | 2  |
+------+----+
| 2005 | 2  |
+------+----+
| 2000 | 3  |
+------+----+
presto sql
1 Answers

This logic produces the results that you want:

select ad.date, df.id
from all_dates ad left join
     interval i
     on ad.date >= i.startd and
        ad.date <= i.endd left join 
     df 
     on ad.date = df.date;

Here is a db<>fiddle.

user_11440350
Related