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.