So I have three columns that are important. CLIENT_ID, START_CONTRACT_DATE, END_CONTRACT_DATE
The objective is to know the minimum date that a client is active regardless of the active contract, but it has to be from subsequente contracts.

I'll try to give examples to best clear this issue, cause it's difficult to explain by words.

Example 1:

Result: 01/01/2012

Example 2:

Result: 13/06/2014

Example 3:

Result: 22/05/2019

NOTE: I'm doing this on SAS Entreprise Guide, so the language I'm using is procsql, however, having the solution in any language would be useful!

CodePudding user response：

Using this you will have the minimum contract start date for every customer. SELECT CLIENT_ID, MIN(START_CONTRACT_DATE) from tbl group by CLIENT_ID

But if you want the biggest period, you can do something like this

SELECT CLIENT_ID, MAX( DATEDIFF ( DAY, START_CONTRACT_DATE, END_CONTRACT_DATE) ) from tbl group by CLIENT_ID

CodePudding user response：

This solution was written for T-SQL, but should show you what to do, assuming procsql has support for recursive ctes (unfortunately I have no experience with procsql):

declare @test table(client_id int, contract_id int, start_contract_date date, end_contract_date date)
INSERT INTO @test VALUES
(2, 5,  '2012-01-01',   '2015-04-05'),
(2, 6,  '2015-04-05',   '2017-06-13'),
(2, 7,  '2017-06-13',   '2019-05-22'),
(2, 8,  '2019-05-22',   '9999-12-31'),
(1, 1,  '2012-01-01',   '2015-04-05'),
(1, 2,  '2015-09-02',   '2017-01-14'),
(1, 3,  '2014-06-13',   '9999-12-31'),
(1, 4,  '2019-03-19',   '2020-08-06'),
(3, 5,  '2012-01-01',   '2015-04-05'),
(3, 6,  '2015-04-05',   '2017-06-13'),
(3, 7,  '2017-06-13',   '2018-05-22'),
(3, 8,  '2019-05-22',   '9999-12-31');

WITH CTE AS (SELECT MIN(
start_contract_date) AS start_contract_date, client_id
FROM @test 
WHERE end_contract_date > GETDATE()
GROUP BY client_id
UNION ALL 
SELECT t.start_contract_Date, t.client_id 
FROM cte c
INNER JOIN @test t ON c.start_contract_date = t.end_contract_date
AND c.client_id = t.client_id
)
SELECT MIN(start_contract_date), client_id 
FROM cte GROUP BY client_id;

Results: