1 query from 2 table to know the last record by date ? (1 Viewer)

[gemma-the-husky]

Note that depending on how your price changes work, you may need to iterate your prices in code to establish the effective price on any given date. Indeed the current price may not be the one with the most recent date, and not just because the latest price might not be effective until some future date.

It's all another reason why it's easier to just have one price table.

 

[cheekybuddha]

you may need to iterate your prices in code to establish the effective price on any given date

I don't think you need to do this. Just limit the prices in the SQL:

SELECT
  p.AddressID,
  p.Price AS CurrentPrice
  p.[Date] AS EffectiveFrom
FROM tblPrices p
INNER JOIN (
  SELECT
    AddressID,
    MAX([Date]) AS EffectiveDate
  FROM tblPrices
  WHERE [Date] <= Now()           -- <-- Adjust as required
  GROUP BY
    AddressID
) AS p1
        ON p.AddressID = p1.AddressID
       AND p.[Date] = p1.EffectiveDate
;

 

[June7]

Adding my 2 cents.

2 tables:

Addresses

Prices - related to Addresses and should have AddressID_FK field, not full address repeated.

Starting price will be first record by oldest date in Prices for each address.

 

[gemma-the-husky]

I don't think you need to do this. Just limit the prices in the SQL:

SELECT
  p.AddressID,
  p.Price AS CurrentPrice
  p.[Date] AS EffectiveFrom
FROM tblPrices p
INNER JOIN (
  SELECT
    AddressID,
    MAX([Date]) AS EffectiveDate
  FROM tblPrices
  WHERE [Date] <= Now()           -- <-- Adjust as required
  GROUP BY
    AddressID
) AS p1
        ON p.AddressID = p1.AddressID
       AND p.[Date] = p1.EffectiveDate
;

The thing is you have to understand whether the prices are changed in chronological sequence.

Eg, you have a current price of £15.00
You enter a new price of £16.00 with effect from 1/1/24. You then renegotiate a new price of £15.50 effective from 1/12/23. This price supersedes the price you entered effective from 1/1/24, so the SP of £16.00 can never be achieved.

Therefore your price selection routine has to pick a price of £15.50 for sales dated in 2024, and I don't think you can do that with a simple query.