3

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.

5

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

enter image description here

2

Since you have defined quarters using the traditional calendar definition, you don't need to store the "end" of a quarter anywhere, particularly once for every single day in a ~90-day period.

(And this could be dangerous if the underlying data is ever expanded to include time, as you may lose all data from the last day of the quarter except that stamped at midnight on that day.)

All you really need to do is calculate the beginning of the next quarter, and only for the actual values from the quarterly table. This avoids a potentially costly join, will work regardless of the data type of DailyTable.[Date], and will work for any non-standard quarter definition as well (provided a quarter doesn't start on something really weird, like the 29th/30th/31st of the month).

SELECT d.ID,
  [Result Date] = d.[Date],
  d.[Value],
  [Most Recent Quarterly Value] = q.[Value]
FROM dbo.QuarterlyTable AS q
INNER JOIN dbo.DailyTable AS d
ON q.ID = d.ID 
AND d.[Date] >= q.[Date] 
AND d.[Date] < DATEADD(QUARTER, 1, q.[Date]);

Now, I've always been a big proponent of a calendar table, and I'm not suggesting it isn't useful to have. This just isn't a complex enough problem to require one IMHO.

  • 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. – SM4 May 11 '16 at 19:58
  • 1
    @SM4 I don't assume 90 days here at all. What I was pointing out in my initial sentence is that with a dates table you're storing the same end of quarter for ~ (which means about) 90 days in each quarter. This has nothing to do with my solution - the only assumption it makes is that your quarterly table uses quarters in the strictest sense of the word (e.g. 3 months), and that they start on some consistent day of the month (<= 28th). If you have "quarters" that don't match what every reasonable person considers a quarter, please add that information to the question. – Aaron Bertrand May 11 '16 at 20:03
1

A date table would be very useful in constructing your query. Look into how to create a data table. Here is a quick video on that, https://www.brentozar.com/archive/2014/12/simply-must-date-table-video/

This query uses a very limited date table to list the Quarter start and end dates for the date you have listed. A real date table would have many more dates loaded.

CREATE TABLE #DailyTable (ID INT, DailyTableDate DATE, Value INT)
CREATE TABLE #QuarterlyTable (ID INT, QuarterlyTableDate DATE, Value INT)
CREATE TABLE #DateTable ([Date] DATE, [QuarterStart] DATE, [QuarterEnd] DATE)

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)

---Very limited example of date table
INSERT INTO #DateTable ([Date],[QuarterStart], [QuarterEnd] ) VALUES ('2010-01-01','2010-01-01','2010-03-31')
INSERT INTO #DateTable ([Date],[QuarterStart], [QuarterEnd] ) VALUES ('2010-02-01','2010-01-01','2010-03-31')
INSERT INTO #DateTable ([Date],[QuarterStart], [QuarterEnd] ) VALUES ('2010-03-01','2010-01-01','2010-03-31')
INSERT INTO #DateTable ([Date],[QuarterStart], [QuarterEnd] ) VALUES ('2010-04-01','2010-04-01','2010-06-30')

SELECT D.ID
, D.DailyTableDate AS 'Result Date'
, D.Value
, QT.Value AS 'Most Recent Quarterly Value'
FROM #DailyTable AS D
JOIN #DateTable AS DT on D.DailyTableDate = DT.Date
JOIN #QuarterlyTable AS QT on DT.QuarterStart = QT.QuarterlyTableDate

DROP TABLE #DailyTable
DROP TABLE #QuarterlyTable
DROP TABLE #DateTable
  • I m looking for a solution that will not require the creation and maintenance of another table. – SM4 May 11 '16 at 19:48
  • 1
    @SM4 While I agree that in this case an additional table is unnecessary, I caution you against dismissing them so easily in general. Numbers and Calendar tables are quite useful to help solve a lot of complex problems, and there really isn't any "maintenance" after they're created. They're also not high overhead because they're typically (a) very narrow and (b) used often enough that they're always in memory. – Aaron Bertrand May 11 '16 at 19:59
  • 1
    @SM4 for some background see this, this, this, this, and some problems I've solved using a calendar table. – Aaron Bertrand May 11 '16 at 20:03
  • The issue is that the start and end date is dependent on the ID. As explained below, I work with financial data so there is no constant calendar. 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. – SM4 May 11 '16 at 20:04
  • 1
    @SM4 If these solutions do not solve the problem for IDs that use something other than a quarter (3 months), please add those cases to the question. We can't read your mind and we can't predict edge cases. For starters, if an ID can have a date range that isn't a quarter, stop calling that table a QuarterlyTable. – Aaron Bertrand May 11 '16 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.