I need to retrieve the point in time cost of an item from a cost history table. The table has the following key columns:
1) Item - Identity
2) Date - integer based on Datediff(d, '1/1/1970, Getdate())
3) Time - integer based on seconds past midnight
4) NewCost - current cost
5) OldCost - previous cost
Scenario: What is the Cost for Item 1 on Date = 9950 Time 750?
Expected Result - Item 1 cost 10 on 9950 at 750.
Create Table:
Select
Item = 1, Date = 9949, Time = 448, NewCost = 1, OldCost = 0
Into #CostHist
Union All
Select 1, 9950, 441, 2, 1
Union all
Select 1, 9950, 700, 3, 2
Union All
Select 1, 9950, 700, 10, 3
Union all
select 1, 9951, 700, 8, 10
Thanks in advance.