Bring Price_at_Date from Price History in Sales Table-CodePudding

Task: merge Sales and Price_History tables in Revenue view while introducing appropriate Price_at_Date from Price_History table

Revenue is a sample view.

Tables:

Price_History:

Product_Name	Price	Price_Change_Date
A	100	2021-09-01
B	120	2021-09-01
A	200	2021-09-05
B	250	2021-09-06

Sales:

Product_Name	Qty	Sales_Date
A	10	2021-09-02
A	12	2021-09-04
B	20	2021-09-03
A	25	2021-09-07
B	30	2021-09-09

Revenue: required

Product_Name	Sale_Qty	Sales_Date	Price_at_Date
A	10	2021-09-02	100
A	12	2021-09-04	100
B	20	2021-09-03	120
A	25	2021-09-07	200
B	30	2021-09-09	250

SQL queries:

CREATE TABLE Price_History 
(
    Product_Name Char(5),
    Price int,
    Price_Change_Date Date
)

INSERT INTO Price_History VALUES ('A', 100, '09-01-2021')
INSERT INTO Price_History VALUES ('B', 120, '09-01-2021')
INSERT INTO Price_History VALUES ('A', 200, '09-05-2021')
INSERT INTO Price_History VALUES ('B', 250, '09-06-2021')

CREATE TABLE Sales 
(
    Product_Name Char(5),
    Qty int,
    Sales_Date Date
)

INSERT INTO Sales VALUES ('A', 10, '09-02-2021')
INSERT INTO Sales VALUES ('A', 12, '09-04-2021')
INSERT INTO Sales VALUES ('B', 20, '09-03-2021')
INSERT INTO Sales VALUES ('A', 25, '09-07-2021')
INSERT INTO Sales VALUES ('B', 30, '09-09-2021')

CREATE TABLE Revenue 
(
    Product_Name Char(5),
    Sale_Qty int,
    Sales_Date Date,    
    Price_at_Date int
)

INSERT INTO Revenue VALUES ('A', 10, '09-02-2021', 100)
INSERT INTO Revenue VALUES ('A', 12, '09-04-2021', 100)
INSERT INTO Revenue VALUES ('B', 20, '09-03-2021', 120)
INSERT INTO Revenue VALUES ('A', 25, '09-07-2021', 200)
INSERT INTO Revenue VALUES ('B', 30, '09-09-2021', 250)

CodePudding user response：

select product_name, sale_qty, sales_date, price "price_at_date"
from (
    select s,*, p.price, 
        row_number() over (partition by s.product_name, s.sales_date order by p.price_change_date desc)
    from sales s
    inner join price_history p on p.product_name = s.product_name and p.price_change_date <= s.sales_date
) t
where t.rn = 1

CodePudding user response：

A simple TOP 1 APPLY should do the trick:

CREATE VIEW Revenue
AS

SELECT
  s.Product_Name,
  s.Qty,
  s.Sales_Date,
  ph.Price
FROM Sales s
OUTER APPLY (
    SELECT TOP 1
      ph.Price
    FROM Price_History ph
    WHERE ph.Product_Name = s.Product_Name
      AND ph.Price_Change_Date <= s.Sales_Date
    ORDER BY ph.Price_Change_Date DESC
) ph;

db<>fiddle