I have a table that contains historical exchange rates: date_time | timestamp xrate | real
There is a maximum of one entry per day, but data are missing on
weekends and holidays. For these missing dates I must use the value
from the previous day (e.g. since data for a Sunday is missing I must
use the value from the Friday just before the weekend).
I have two questions:
1) Since historical exchange rates are not supposed to change I
thought about creating a new table with data for all the missing dates
(calculated using some procedural language). However, I would be much
happier if there was a way to do this using SQL in a SELECT statement.
Do you have any hints for this?
2)
I have a number of other tables where data may be missing for
different reasons. These data may be on a daily or an hourly basis.
When a user selects a range of data from e.g. the 1st of January to
the 1st of February I would like to be able to return a full set of
data where all missing entries are returned as NULL. Is there a smart
way to do this using SQL?
Any hints or references you may have on the subject of handling
missing data in time series data are very welcome.
If there is a smarter way to set up tables for handling this type of
data then please enlighten me.
Thanks
Lars