I have an opp table with the customer's purchases:
| CLOSED_DATE | OPP_AMOUNT | |
|---|---|---|
| 0 | 1/4/2022 | 6600 |
| 1 | 2/17/2022 | 9125 |
and a charges table with dates:
| CHARGE_DATE | |
|---|---|
| 0 | 2022-01-03 0:00:00 |
| 1 | 2022-01-04 0:00:00 |
| 2 | 2022-03-15 0:00:00 |
| 3 | 2022-03-15 0:00:00 |
| 4 | 2022-04-15 0:00:00 |
| 5 | 2022-04-15 0:00:00 |
for each charge, I'd like to have the total amount of purchases on that point of time (and the amount of first purchase for dates before it):
| CHARGE_DATE | Target | |
|---|---|---|
| 0 | 2022-01-03 0:00:00 | 6600 |
| 1 | 2022-01-04 0:00:00 | 6600 |
| 2 | 2022-03-15 0:00:00 | 15725 |
| 3 | 2022-03-15 0:00:00 | 15725 |
| 4 | 2022-04-15 0:00:00 | 15725 |
| 5 | 2022-04-15 0:00:00 | 15725 |
tried join on charge_date<=closed_date but this brings the numbers multiple time, any ideas?
CodePudding user response：
One approach can be as follows -
select distinct c.id,charge_date, sum(amount) over (partition by c.id order by c.id)
as target from charge_date c left outer
join closed_date d on closed_date<=charge_date order by c.id;
---- ------------------------- --------
| ID | CHARGE_DATE | TARGET |
|---- ------------------------- --------|
| 0 | 2022-01-03 00:00:00.000 | NULL |
| 1 | 2022-01-04 00:00:00.000 | 6600 |
| 2 | 2022-03-15 00:00:00.000 | 15725 |
| 3 | 2022-03-15 00:00:00.000 | 15725 |
| 4 | 2022-04-15 00:00:00.000 | 15725 |
| 5 | 2022-04-15 00:00:00.000 | 15725 |
---- ------------------------- --------