Ho to view periodic relationship data
In the previous post, I exposed a method for uniquely indexing time spans of periodic-memberships.
Here, we will discuss the next task: How to view historical data in such structures?
To illustrate this, we will talk about a portfolio sample data, simply because this is a more elaborate case (than the hotel/room reservation case).
Suppose we have:
§ A portfolio named ‘Sample portfolio’
§ A set of companies: company 1, company 2 and company 3
§ A set of daily historical data for each company for the period from 2010/01/01 to 2010/01/10
Now suppose that:
§ Company 1 and company 3 are ‘permanent members’ of the portfolio starting at 2010/01/01
§ Company 2 is a member of the portfolio for the following periods:
o 2010/01/01 to 2010/01/05
o 2010/01/08 to à endless
To query the daily historical data for company 2 related to its parent portfolio, we should thus obtain the data for the periods where it is effectively a member of the portfolio:
§ 2010/01/01, 2010/01/02, 2010/01/03, 2010/01/04, 2010/01/05
§ 2010/01/08, 2010/01/09 and 2010/01/10
(Note that data for 2010/01/06 and 2010/01/07 should be ignored)
This can be done using a view similar to the following (TSQL followed by the view design diagram)
SELECT p.name AS portfolio,
c.name AS company,
history.date,
history.data
FROM dbo.portfolio_companies AS pc INNER JOIN
dbo.portfolios AS p ON pc.portfolio_id = p.id INNER JOIN
dbo.company_data AS history
ON pc.date_start <= history.date
AND ISNULL(pc.date_end, CONVERT(datetime, '9999/01/01')) >= history.date INNER JOIN
dbo.companies AS c ON history.company_id = c.id AND pc.company_id = c.id
As said above, company 2 is a portfolio member during the following periods:
§ 2010/01/01 to 2010/01/05
§ 2010/01/08 to à endless
With our sample history data, this view produces the following data for company 2 (8 entries):
Note: the history data table contains the following data for company 2 (10 entries):
