Joining two tables with date ranges

hvempati — Fri, 12 Oct 2018 05:34:13 GMT

I have two tables.

Table A

id	start_dt	end_dt
15	05Sep2018	12-Sep-18
15	10Sep2018	20-Sep-18
15	11Sep2018	12-Sep-18
16	05Sep2018	20-Sep-18
17	06Sep2018	6-Sep-18
17	12Sep2018	20Sep2018

Table B

Dates	Sales
05Sep2018	81
06Sep2018	95
07Sep2018	21
08Sep2018	23
09Sep2018	48
10Sep2018	78
11Sep2018	13
12Sep2018	10
13Sep2018	91
14Sep2018	60
15Sep2018	30
16Sep2018	81
17Sep2018	4
18Sep2018	11
19Sep2018	5
20Sep2018	77

I need to join these two tables such that it adds all the sales within the date range with respect to id. If there are multiple entry with id for same date, it needs to be divided equally among the id's.

Output table calculation would look like this-

id	start_dt	end_dt	sales
15	05Sep2018	12-Sep-18	81+95+21+23+48+78/2+13/3+10/3
15	10Sep2018	20-Sep-18	78/2+13/3+10/3+91+60+30+81+4+11+5+77
15	11Sep2018	12-Sep-18	13/3+10/3
16	05Sep2018	20-Sep-18	81+95+21+23+48+78+13+10+91+60+30+81+4+11+5+77
17	06Sep2018	6-Sep-18	95
17	12Sep2018	20Sep2018	10+91+60+30+81+4+11+5+77

and the output table should look like this-

id	start_dt	end_dt	sales
15	05Sep2018	12-Sep-18	314.6667
15	10Sep2018	20-Sep-18	405.6667
15	11Sep2018	12-Sep-18	7.666667
16	05Sep2018	20-Sep-18	728
17	06Sep2018	6-Sep-18	95
17	12Sep2018	20Sep2018	369

Any help is highly appreciated and would love to clarify if you have any questions.

Thank you.

Re: Joining two tables with date ranges

LinusH — Fri, 12 Oct 2018 06:03:02 GMT

Without calculating on your example I would simply suggest a join with a BETWEEN - AND criteria, and then just SUM() - GROUP BY.

Re: Joining two tables with date ranges

hvempati — Fri, 12 Oct 2018 16:04:42 GMT

But is there not a way to do this type of join or can I split the data and then join?

topic Re: Joining two tables with date ranges in New SAS User

Joining two tables with date ranges

Re: Joining two tables with date ranges

Re: Joining two tables with date ranges