4

Imagine I have a database with historic price data and a second table with dates

Item | Price | Date               Customer | Item | date
-------------------------         ---------------------------
   A | 2.49$ | 2014-09-01                1 |    A | 2014-08-27
   B | 1.29$ | 2014-09-01                2 |    A | 2014-09-02
   A | 2.99$ | 2014-08-25                
   B | 1.39$ | 2014-08-26

A new row was entered into the price history table every time the price changed.

How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?

SQLFiddle

3
  • 1
    Can you, please, post a sample schema and data on sqlfiddle?
    – Marian
    Commented Oct 9, 2014 at 13:02
  • Are you able to alter the schema of this database? I ask because it would be much better if you a start and end date on the price table. Commented Oct 9, 2014 at 13:41
  • No, this is a simplified example. In the real world the price list is being exported weekly for all items, and if a week is missing I want to fall back to the data of the previous week.
    – Twinkles
    Commented Oct 9, 2014 at 13:52

1 Answer 1

2
select *
from dbo.Sales s
  cross apply (
  select top (1) *
  from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date
  order by ph.Date desc
) ca;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.