I need to find the active price for item from a price history table. I'm really battling with the logic and would appreciate some help. I've tried some of the solutions on this site but could not find a query that works for me.
Here's an example:
Table A is my master item table. Table B is my price history table, this table contains past, current and future prices.
I need to join table B to table A on the item number and select the current active price.
Table A
| Item |
|---|
| 123 |
| 456 |
Table B
| Item | Price | Start Date | End Date |
|---|---|---|---|
| 123 | 50 | 2021-01-01 | 2021-11-30 |
| 123 | 100 | 2021-12-01 | 2022-01-31 |
| 123 | 200 | 2022-02-01 | 2022-12-31 |
| 456 | 75 | 2021-02-01 | 2021-10-30 |
| 456 | 125 | 2021-11-01 | 2022-03-20 |
| 456 | 250 | 2022-03-21 | 2022-12-31 |
So my requests should look like the below
| Item | Price |
|---|---|
| 123 | 100 |
| 456 | 125 |
CodePudding user response：
Compare the current date to the start and end range:
SELECT a.Item, b.Price
FROM TableA a
INNER JOIN TableB b
ON b.Item = a.Item
WHERE GETDATE() BETWEEN b.StartDate AND b.EndDate;
CodePudding user response：
You can do this using Table B only:
select item,
price
from "Table B"
where getdate() between "Start Date" and "End Date"
This assumes you don't need items in Table A that don't appear in Table B, and that there are no overlapping date ranges.