Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value.
As the names indicate, the frequency of data stored in DailyTable is daily, while the frequency of data stored in QuarterlyTable is quarterly.
How can I join the two tables such that I get daily results combining the most recent (point in time) data from each table based on ID and Date?
DailyTable
ID | Date | Value |
---------+---------+------------
1 |1/1/2010 | 10 |
1 |1/2/2010 | 15 |
...
1 |3/1/2010 | 20 |
...
1 |4/1/2010 | 30 |
QuarterlyTable
ID | Date | Value |
---------+---------+------------
1 |1/1/2010 | 1000 |
1 |4/1/2010 | 2000 |
...
Result
ID | Date | Value | Most Recent Quarterly Value
---------+---------+-------------------------------------------
1 |1/1/2010 | 10 | 1000
1 |1/2/2010 | 15 | 1000
...
1 |3/1/2010 | 20 | 1000
...
1 |4/1/2010 | 30 | 2000
I am dealing with financial data, where the daily table stores stock prices, volume etc., while the quarterly table stores financial information such as net income, revenues. The update frequency of quarterly data depends on each company so it is not safe to assume that quarters have 90 days distance.
The start and end date is dependent on the ID. QuarterStart date is the earnings announcement date which is different for each ID. The table has 3,000 IDs. QuarterEnd date is simply next QuarterStart date - 1 business day (in the financial industry, Fiscal and Calendar quarter are two similar but not identical terms. Both are quarters, but calendar year is based on constant date ranges, while Fiscal is based on announcements).
I m looking for a solution that will not require the creation and maintenance of another table.