SQL - 选择已结束最近契约(Contract)但还有其他契约(Contract)未结的员工

Question

我一直在兜圈子，试图弄清楚这个问题。

我正在尝试选择已结束最近契约(Contract)但仍保留上一份有效契约(Contract)的员工。

例如，一名员工拥有多份契约(Contract)(有些可能是临时契约(Contract)或兼职契约(Contract) - 这无关紧要)，但终止了最近的契约(Contract)，但他们仍继续保留较旧的契约(Contract)。

请参阅下表了解我想要实现的目标 - 以及相关字段:

+------+-------------+-------------+------------+------------+
| 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 |
+------+-------------+-------------+------------+------------+

这是我当前拥有的代码，它没有返回正确的数据:

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

这就是代码的结果(示例):

+------+-------------+-------------+------------+------------+
| 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       |
+------+-------------+-------------+------------+------------+

正如您所看到的，最近的契约(Contract)没有结束日期，但有一个未结契约(Contract)。

非常感谢任何帮助。

Answer 1

使用 cross apply()使用窗口聚合函数 count() 获取最近的 start_date、end_date 和 open_contracts 计数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

<小时/>

使用一些附加案例测试设置:

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 演示:http://rextester.com/BUYKJ77928

返回:

+------+-------------+-------------+------------+------------+----------------+--------------+----------------+
|  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 |
+------+-------------+-------------+------------+------------+----------------+--------------+----------------+