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.
Advertisement
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 |