SQL - Selecting employees that have ended most recent contract but have other contracts open

Question

I've been going around in circles trying to figure this one out.

I'm trying to select employees who have ended their most recent contract but have an active contract still open from previous.

For example, an employee has several contracts (some may be temporary or part time - this is irrelevant) but ends their most recent contract, however, they still continue to be in their older contracts.

Please see the table below as to what I'm trying to achieve - with relevant fields:

+------+-------------+-------------+------------+------------+
| ID   | CONTRACT_ID | EMPLOYEE_ID | START_DATE | END_DATE   |
+------+-------------+-------------+------------+------------+
| 4321 | 974         | 321         | 21/01/2004 | 31/12/2016 |
+------+-------------+-------------+------------+------------+
| 4322 | 1485        | 321         | 09/01/2009 | 31/08/2014 |
+------+-------------+-------------+------------+------------+
| 4323 | NULL        | 321         | 25/07/2009 | 31/01/2010 |
+------+-------------+-------------+------------+------------+
| 4324 | 2440        | 321         | 01/06/2012 | NULL       |
+------+-------------+-------------+------------+------------+
| 4325 | 7368        | 321         | 01/01/2017 | NULL       |
+------+-------------+-------------+------------+------------+
| 4326 | 7612        | 321         | 14/02/2017 | 06/06/2017 |
+------+-------------+-------------+------------+------------+

Here is the code I currently have, which is not bringing back the correct data:

select
cond.EMPLOYEE_ID
,cond.END_DATE

from
contracts as cond

join
(select

EMPLOYEE_ID
,START_DATE
,END_DATE

from
contracts

where
END_DATE is null) a on a.EMPLOYEE_ID = cond.employee_id and a.START_DATE <     
cond.END_DATE

group by cond.end_date, cond.EMPLOYEE_ID

having
max(cond.START_DATE) is not null AND cond.END_DATE is not null

This is what the code results in (example):

+------+-------------+-------------+------------+------------+
| ID   | CONTRACT_ID | EMPLOYEE_ID | START_DATE | END_DATE   |
+------+-------------+-------------+------------+------------+
| 1234 | NULL        | 123         | 03/12/2014 | 26/10/2015 |
+------+-------------+-------------+------------+------------+
| 1235 | NULL        | 123         | 30/10/2015 | 28/01/2016 |
+------+-------------+-------------+------------+------------+
| 1236 | NULL        | 123         | 06/11/2015 | 28/01/2016 |
+------+-------------+-------------+------------+------------+
| 1237 | 1234        | 123         | 07/03/2016 | NULL       |
+------+-------------+-------------+------------+------------+
| 1238 | NULL        | 123         | 04/04/2017 | 13/04/2017 |
+------+-------------+-------------+------------+------------+
| 1239 | NULL        | 123         | 18/04/2017 | NULL       |
+------+-------------+-------------+------------+------------+

As you can see, the most recent contract does not have an end date but there is an open contract.

Any help much appreciated.

Answer 1

using cross apply() to get the most recent start_date, end_date, and the count of open_contracts using a windowed aggregate function count() over() :

select 
    c.id
  , c.contract_id
  , c.employee_id
  , start_date    
  , end_date      
  , max_start_date = x.start_date
  , max_end_date   = x.end_date
  , x.open_contracts
from contracts c
  cross apply (
    select top 1
        i.start_date
      , i.end_date
      , open_contracts = count(case when i.end_date is null then 1 end) over(partition by i.employee_id)
    from contracts i
    where i.employee_id = c.employee_id
    order by i.start_date desc
    ) x
where x.end_date is not null
  and x.open_contracts > 0
order by c.employee_id, c.start_date asc

---

test setup with some additional cases:

create table contracts (id int, contract_id int, employee_id int, start_date date, end_date date);
insert into contracts values
 (4321, 974, 321, '20040121', '20161231')
,(4322, 1485, 321, '20090109', '20140831')
,(4323, null, 321, '20090725', '20100131')
,(4324, 2440, 321, '20120601', null)
,(4325, 7368, 321, '20170101', null)
,(4326, 7612, 321, '20170214', '20170606')
,(1, 1, 1, '20160101', null)
,(2, 2, 1, '20160701', '20161231')
,(3, 3, 1, '20170101', null)        /* most recent is open, do not return */
,(4, 4, 2, '20160101', '20170630')
,(5, 5, 2, '20160701', '20161231')
,(6, 6, 2, '20170101', '20170630')  /* most recent is closed, no others open, do not return */
,(7, 7, 3, '20160101', '20170630')
,(8, 8, 3, '20160701', null)
,(9, 9, 3, '20170101', '20170630')  /* most recent is closed, one other open, return */
;

rextester demo: http://rextester.com/BUYKJ77928

returns:

+------+-------------+-------------+------------+------------+----------------+--------------+----------------+
|  id  | contract_id | employee_id | start_date |  end_date  | max_start_date | max_end_date | open_contracts |
+------+-------------+-------------+------------+------------+----------------+--------------+----------------+
|    7 | 7           |           3 | 2016-01-01 | 2017-06-30 | 2017-01-01     | 2017-06-30   |              1 |
|    8 | 8           |           3 | 2016-07-01 | NULL       | 2017-01-01     | 2017-06-30   |              1 |
|    9 | 9           |           3 | 2017-01-01 | 2017-06-30 | 2017-01-01     | 2017-06-30   |              1 |
| 4321 | 974         |         321 | 2004-01-21 | 2016-12-31 | 2017-02-14     | 2017-06-06   |              2 |
| 4322 | 1485        |         321 | 2009-01-09 | 2014-08-31 | 2017-02-14     | 2017-06-06   |              2 |
| 4323 | NULL        |         321 | 2009-07-25 | 2010-01-31 | 2017-02-14     | 2017-06-06   |              2 |
| 4324 | 2440        |         321 | 2012-06-01 | NULL       | 2017-02-14     | 2017-06-06   |              2 |
| 4325 | 7368        |         321 | 2017-01-01 | NULL       | 2017-02-14     | 2017-06-06   |              2 |
| 4326 | 7612        |         321 | 2017-02-14 | 2017-06-06 | 2017-02-14     | 2017-06-06   |              2 |
+------+-------------+-------------+------------+------------+----------------+--------------+----------------+