Creating a combined timeline based on several timelines in several tables
642805Apr 20 2011 — edited Apr 20 2011Hi,
I need to extract a timeline for a customer based on valid_from and valid_to dates in several tables.
For example: I have a table named customers with an id, a valid_from and a valid_to date and a table named contracts with an contrat_name, customer_id and valid_from and valid_to:
CUSTOMERS:
ID | VALID_FROM | VALID_TO
1 | 01.03.2010 | 01.01.4000
CONTRACTS:
CONTRACT_NAME | CUSTOMER_ID | VALID_FROM | VALID_TO
ContractA | 1 | 01.03.2010 | 01.10.2010
ContractB | 1 | 01.10.2010 | 01.01.4000
The following statement would now give me the correct timeline:
select cus.id customer, con.contract_name contract, greatest(cus.valid_from,con.valid_from) valid_from, least(cus.valid_to,con.valid_to) valid_to
from customers cus
inner join contracts con on cus.id = con.customer_id;
CUSTOMER | CONTRACT | VALID_FROM | VALID_TO
1 | ContractA | 01.03.2010 | 01.10.2010
1 | ContractB | 01.10.2010 | 01.01.4000
That works, but I get a problem as soon as I have a point of time where there is no contract for a customer but I still would like to have these periods in my timeline:
Let's assume the following data and the same select statement:
CUSTOMERS:
ID | VALID_FROM | VALID_TO
1 | 01.03.2010 | 01.01.4000
CONTRACTS:
CONTRACT_NAME | CUSTOMER_ID | VALID_FROM | VALID_TO
ContractA | 1 | 01.05.2010 | 01.10.2010
ContractB | 1 | 01.12.2010 | 01.03.2011
What I would now get would be:
CUSTOMER | CONTRACT | VALID_FROM | VALID_TO
1 | ContractA | 01.05.2010 | 01.10.2010
1 | ContractB | 01.12.2010 | 01.03.2011
But what I would like to get is the following:
CUSTOMER | CONTRACT | VALID_FROM | VALID_TO
1 | null | 01.03.2010 | 01.05.2010
1 | ContractA | 01.05.2010 | 01.10.2010
1 | null | 01.10.2010 | 01.12.2010
1 | ContractB | 01.12.2010 | 01.03.2011
1 | null | 01.03.2011 | 01.01.4000
What I do not want to do is to generate a result with contract = null any time there is no contract since I actually want to join the timeline of several different tables into one and it would therefore become very complicated to assume things based on what data can or can not be found in one specific table.
Thanks for any help or ideas,
Regards,
Thomas