SQL – Sélection des employés ayant terminé le contrat le plus récent mais ayant d'autres contrats ouverts

J'ai tourné en rond en essayant de comprendre celui-ci.

J'essaie de sélectionner les employés qui ont mis fin à leur contrat le plus récent mais qui ont un contrat actif encore ouvert par rapport à l'année précédente.

Par exemple, un employé a plusieurs contrats (certains peuvent être temporaires ou à time partiel – ce n'est pas pertinent), mais il met fin à son contrat le plus récent, mais il continue d'être dans ses anciens contrats.

S'il vous plaît voir le tableau ci-dessous pour ce que j'essaie d'atteindre – avec des champs pertinents:

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

Voici le code que j'ai actuellement, qui ne ramène pas les bonnes données:

 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

C'est ce que le code résulte en (exemple):

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

Comme vous pouvez le voir, le contrat le plus récent n'a pas de date de fin mais il y a un contrat ouvert.

Toute aide très appréciée.

en utilisant cross apply() pour get le plus récent start_date , end_date et le nombre de open_contracts utilisant une fonction d'agrégation fenêtrée 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

configuration d'essai avec quelques cas supplémentaires:

 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

résultats:

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

Je ne suis pas un expert en SQL Server, mais vous pouvez essayer quelque chose de similaire:

 SELECT * FROM contracts cont WHERE cont.end_date IS NOT NULL AND cont.end_date <= SYSDATE AND NOT EXISTS (SELECT * FROM contracts recent WHERE recent.employee_id = cont.employee_id AND recent.start_date > cont.start_date) AND EXISTS (SELECT * FROM contracts openc WHERE openc.employee_id = cont.employee_id AND (openc.end_date IS NULL OR openc.end_date > SYSDATE))

Les 2 premières conditions searchnt des contrats fermés.
Le suivant ("NOT EXISTS") s'assure que le contrat sélectionné est le plus récent.
La dernière partie assure qu'il existe d'autres contrats ouverts.

Essayez ce mec.

 SELECT [EMPLOYEE_ID] FROM [contracts] WHERE [END_DATE] IS NULL AND [EMPLOYEE_ID] IN (SELECT B.[EMPLOYEE_ID] FROM ( SELECT * FROM ( SELECT RowN = Row_Number() over (partition by [EMPLOYEE_ID] ORDER BY[START_DATE] DESC) , [EMPLOYEE_ID] , [CONTRACT_ID] , [END_DATE] FROM [contracts] ) A WHERE A.[END_DATE] IS NOT NULL AND A.[RowN] = 1) B)

Vous pouvez le faire avec ROW_NUMBER() et un CTE

Voyez-le en action: http://rextester.com/HQVXF56741

Dans le code ci-dessous, j'ai changé le format de date que vous n'avez pas à faire.

 set dateformat dmy declare @table table (ID int,CONTRACT_ID int, EMPLOYEE_ID int, [START_DATE] datetime, END_DATE datetime) insert into @table values (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') --this applies a row_number to each contract per employee --the most recent contract (by start date) gets a 1 ;with cte as( select EMPLOYEE_ID ,ID ,row_number() over (partition by EMPLOYEE_ID order by [START_DATE] desc) as ContractRecentcy from @table) --this will return all contacts that are open, which aren't the most recent for the employee. select t.* from @table t where t.END_DATE is null and t.ID not in (select ID from cte where ContractRecentcy = 1) set dateformat mdy