选择使用最近日期的数据

Question

我有如下两个表:

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

表

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

我必须计算日期"2017-07-28"的每个ID的总价格(价格x数量)，但条件是:如果给定日期没有价格可用，则应使用最接近但在日期之前的价格.

我已经尝试了下面的查询，但它没有给出正确的输出。

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'

计算2014年7月27日的"C"价格。

Answer 1

只需使用子查询获取相关价格，然后相乘:

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;

这回报:

tbody> <<tr>

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