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

  • 1
    Can you, please, post a sample schema and data on sqlfiddle? – Marian Oct 9 '14 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. – James Anderson Oct 9 '14 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 Oct 9 '14 at 13:52
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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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