Sql-server – Join between different frequency tables

greatest-n-per-groupjoin;sql serversql-server-2008

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.

Best Answer

Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date.

This solution also does not require a calendar table and makes no assumption about the duration of a quarter. However, it does make an assumption that you have no gaps in your quarterly table. For example, if you were missing a year's worth of quarters, the solution would identify the "most recent quarter" as the quarter that happened a year ago. This may be perfectly fine, but you should at least be aware of the assumption.

CREATE TABLE #DailyTable (ID INT, DailyTableDate DATE, Value INT)
CREATE TABLE #QuarterlyTable (ID INT, QuarterlyTableDate DATE, Value INT)
ALTER TABLE #QuarterlyTable ADD UNIQUE CLUSTERED (ID, QuarterlyTableDate)

INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-01-01',10)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-02-01',15)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-03-01',20)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-04-01',30)

INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-01-01',1000)
INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-04-01',2000)

SELECT d.ID,
    d.DailyTableDate AS Result_Date,
    d.Value,
    qt.Value AS Most_Recent_Quarterly_Values
FROM #DailyTable AS d
CROSS APPLY (
    -- Find the value for latest quarter that ended on or before the daily date
    SELECT TOP 1 q.Value
    FROM #QuarterlyTable q
    WHERE q.ID = d.ID
        AND q.QuarterlyTableDate <= d.DailyTableDate
    ORDER BY q.QuarterlyTableDate DESC
) qt

Related Solutions

Sql-server – a good way to query data stored in multiple identical tables split by date/time

Personally, I'd consider using triggers to load a proper single table and query that. Then you have control of partitioning (if needed), defining good indexes etc

Depending on volumes, you could use service broker to decouple the app and your tables.

Basically, anything to avoid massive UNIONs and dynamic SQL...

Mysql – How to simulate a table join on itself

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
( 
  -- list of every date
  select distinct date
  from yourtable 
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |