Need help with Query on Historical Data

• John-79567

  SSCommitted

  Points: 1892

  More actions

  April 20, 2007 at 2:55 am

  #370605

  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.

• John-79567

  SSCommitted

  Points: 1892

  More actions

  April 20, 2007 at 3:38 am

  #701659

  I solved the problem using this query.  I'm not sure how it will perform yet, so I'll have to let you all know later.

  Select Item, Date, Time, NewCost, OldCost

  From #CostHist a

  Left Join #CostHist b

  on a.item = b.item and a.date = b.date and a.time = b.time and a.newcost = b.oldcost

  Where a.Item = 1

  and a.date <=9950

  and a.time <=750

  and b.date is null