CODEWITHSUNDEEP
sqlvertica
Bernardo
Bernardo

Reputation: 3348

SQL join based on Date

I have two tables: Table A

+-------+----------+
| prop  | str_date |
+-------+----------+
| AL408 | 3/1/2009 |
| AL408 | 4/1/2009 |
| AL408 | 5/1/2009 |
| AL408 | 6/1/2009 |
+-------+----------+

Table B

+---------+-----------+----------+
| prop_id | agrx_date | brand_id |
+---------+-----------+----------+
| AL408   | 5/5/1986  | CI       |
| AL408   | 6/30/1994 | CI       |
| AL408   | 5/3/1999  | CI       |
| AL408   | 4/21/2006 | CI       |
| AL408   | 3/20/2009 | QI       |
+---------+-----------+----------+

I'd like pull in brand_id into my result query but the brand_id changes accordingly by comparing str_date to agrx_date. For the month after a brand_id has changed via the agrx_date, the result would reflect that new brand_id. All str_dates are monthly values.

The end result would look like this:

+-------+----------+--------+
| prop  | str_date | Result |
+-------+----------+--------+
| AL408 | 3/1/2009 | CI     |
| AL408 | 4/1/2009 | QI     |
| AL408 | 5/1/2009 | QI     |
| AL408 | 6/1/2009 | QI     |
+-------+----------+--------+

Here's what I have so far (which is not correct) and I'm not sure how to get my end result. 

select
a.prop
,a.str_date
,b.agrx_date
,b.brand_id

from tableA a
left join tableB b
on a.prop = b.prop_id
and a.str_date < b.agrx_date

where a.prop = 'AL408'

I'm passing this through Tableau so I cannot use CTE or other temp tables.

Upvotes: 1

Views: 49

Answers (2)

woot
woot

Reputation: 7616

You could create a date range using a lead() analytical function. The date range could then be used as part of a theta join to pull in the correct brand. This is a pretty simple way to pull the date value from the next record, see the definition of next_agrx_date below.

The range would be inclusive for the start (>=), but noninclusive on the end (<). You also need to handle the null case for open-ended ranges. You can find this logic in the join below.

select
   a.prop
  ,a.str_date
  ,b.agrx_date
  ,b.brand_id
from tableA a 
left join
( select 
     prop
    ,agrx_date
    ,brand_id
    ,lead(agrx_date) over (partition by prop order by agrx_date) next_agrx_date 
  from tableB ) b 
on (b.prop = a.prop and a.str_date >= b.agrx_date and (a.str_date < b.next_agrx_date or b.next_agrx_date is null))
order by prop, str_date

Upvotes: 1

RemanBroder
RemanBroder

Reputation: 117

You can use DATE_FORMAT to change the dates to match formatting.

Example

DATE_FORMAT(str_date,'%m-%d-%Y')

or whatever field and format you want to use.

Upvotes: 0

Related Questions