Oracle historical reporting - what was the row at a point in time

Question

I have been asked to run a report of the state of our assets at a fixed point in time (1st Jan 2019).

The way this database has been written is that the asset has its own table with current info and then for various bits of data there is also the history of that info changing, each bit is stored its own "history" table with a start and end date. So for example one of the bits of info is the asset class - the asset table will have a field that contains the current asset class and then if that class has changed in the past then there will be rows in the asset_history table with start and end dates. Something like...

AssetID AssetClass StartDate EndDate
------- ---------- --------- -------
      1          1 12-12-87  23-04-90
      1          5 23-04-90  01-02-00
      1          2 01-02-00  27-01-19
      1          1 27-01-19

So this asset has changed classes a few times but I need to write something to be able to check, for each asset, and work out which class was the active class as at 1st Jan. For this example that would be the second-from last row as it changed to class 2 back in 2000 and then after 1st Jan 2019 it became a class 1.

And to make it more complicated I will need this for several bits of data but if I can get the notion of how to do it right then I'm happy to translate this to the other data.

Any pointers would be much appreciated!

Answer 1

I usually write this like

select assetClass
from history_table h
 where :point_in_time >= startDate
   and (:point_in_time < endDate
        or endDate is null)

(assuming that those columns are actually date type and not varchar2)

between always seems tempting, but it includes both endpoints, so you'd have to write something like :point_in_time between startDate and (endDate - interval '1' second)

EDIT: If you try to run this query with a point_in_time before your first start_date, you won't get any results. That seems normal to me, but maybe instead you want to pick "the first result which hasn't expired yet", like this:

select assetClass
from history_table h
 where (:point_in_time < endDate
        or endDate is null)
order by startDate asc
fetch first 1 row only