I am using postgresql, and I have those two tables, Sale and Royalty.
Sale
| saleId | ItemId | price | createdAt |
|---|---|---|---|
| 1 | a | 200 | 2022-08-17 |
| 2 | b | 400 | 2022-08-19 |
| 3 | c | 500 | 2022-09-04 |
Royalty
| Id | rate | createdAt | deletedAt |
|---|---|---|---|
| 1 | 0.25 | 2022-08-10 | 2022-08-20 |
| 2 | 0.15 | 2022-08-20 | 2022-09-01 |
| 3 | 0.20 | 2022-09-01 | null |
I want to join sale and royalty to make result like this. the point is how to match rate with Sale.createdAt comparing to Royalty's rate period.
selected Result
| ItemId | rate*price | Sale.createdAt |
|---|---|---|
| a | 50 (200*0.25) | 2022-08-17 |
| b | 100 (400*0.25) | 2022-08-19 |
| c | 100 (500*0.20) | 2022-09-04 |
I don't want to use between on every royalty since more rows could be added.
I'm considering making Sale-Royalty table to get rate*price easily,
but I wonder if there's a way to solve using join with this condition...
CodePudding user response：
One approach is to utilize postgres' daterange type with its <@ operator :
select
s.*,
r.rate,
s.price * rate as value
from sale s
join royalty r on s.createdAt <@ daterange(r.createdAt, r.deletedAt)
;
caveats :
- if royalty date ranges overlap, this will multiply the returned rows (a sale having several valid royalty ranges will appear n times)
- replace with an outer (left) join if you need sales even without royalties