CODEWITHSUNDEEP
sqlamazon-redshift
scott martin
scott martin

Reputation: 1293

Redshift - Joining two tables based on date ranges

I have a table that stores date when product was bought and another table that states what offers were run during different time frames.

I am trying to join both these tables based on dates to find out if there was any sale during the date when purchase was made.

Given below is how much looks like:

Sale data:

prod_id,sale_date
1001,2019-02-02
1002,2019-05-04
1003,2019-03-04
1004,2019-04-21

Table that stores offers run by date is as below:

offer_name, start_date, end_date
offer_1,2019-01-02,2019-03-01
offer_2,2019-02-02,2019-03-06
offer_3,2019-04-01,2019-05-01
offer_4,2019-06-01,2019-07-01

I am trying to link the Sale data with the date when sale run data to get the below output:

prod_id,sale_date,offer_name
1001,2019-02-02,offer_1
1002,2019-05-04,
1003,2019-03-04,offer_2
1004,2019-04-21,offer_3

Upvotes: 0

Views: 1480

Answers (1)

DrxziS
DrxziS

Reputation: 36

Its quite simple. You can simply use between to get the offer_name.

SELECT S.PROD_ID,S.SALE_DATE,O.OFFER_NAME
FROM Sale_data S
JOIN offers_run_data O
 ON S.SALE_DATE BETWEEN O.START_DATE AND O.END_DATE

Upvotes: 2

Related Questions