Select data using closest date

I have two tables like below:

table1

Date             ID          Price
2014-01-05        B          174.52
2014-02-25        B          181.68
2014-07-28        B          179.82
2014-02-01        C          39.84
2014-07-27        C          42.70
2014-01-30        D          121.63
2014-07-28        D          136.99

table2

Date             ID   Quantity
2014-01-31       B      100
2014-07-28       B      200
2014-02-01       C      250
2014-07-28       C      250
2014-01-31       D      300

I have to calculate TotalPrice (price x Quantity) from each ID for the date ‘2017-07-28’ but condition is that: if no price is available for a given date, the price closest to but before the date should be used.

I have tried the below query but its not giving the proper output.

select *, (t1.Price * t2.Quantity) as TotalPrice
from tab1(nolock) t1
inner join tab2(nolock) t2 on t1.ID = t2.ID and t1.date = t2.date
where t1.AsOfdate = '2017-07-28'

For calculation of ‘C’ price of 2014-07-27 should be taken.

Answer

Just use a sub-query to obtain the relevant price and then multiply:

create table #table1 (AsOfDate date, ID varchar(50), Price money);
create table #table2 (AsOfDate date, ID varchar(50), Quantity int);

insert into #table1 (AsOfDate, ID, Price)
values
('2017-01-31', 'A', 174.52),
('2017-02-15', 'A', 181.68),
('2017-02-28', 'A', 179.82),
('2017-02-01', 'B', 39.84),
('2017-02-27', 'B', 42.1),
('2017-01-30', 'C', 121.4),
('2017-02-28', 'C', 136.7);

insert into #table2 (AsOfDate, ID, Quantity)
values
('2017-01-31', 'A', 100),
('2017-02-28', 'A', 200),
('2017-02-01', 'B', 250),
('2017-02-28', 'B', 350),
('2017-01-31', 'C', 650);

with cte as (
    select [Date], ID, Quantity
      , (
        select top 1 Price
        from #table1 T1
        where T1.ID = T2.ID
        and T1.[Date] <= T2.[Date]
        order by T1.[Date] desc
      ) Price
    from #table2 T2
    where t2.AsOfdate <= '2017-07-28'
)
select [Date], ID, Quantity * Price TotalPrice
from cte;

This returns:

AsOfDate	ID	TotalPrice
2017-01-31	A	17452.00
2017-02-28	A	35964.00
2017-02-01	B	9960.00
2017-02-28	B	14735.00
2017-01-31	C	78910.00