All geek questions in one place

SQL - the choice of employees who completed the most recent contract, but opened other contracts

I gathered in circles trying to figure it out.

I am trying to select employees who have completed their last contract, but have an active contract that is still open from the previous one.

For example, an employee has several contracts (some of them may be temporary or incomplete - it does not matter), but their last contract ends, however, they still remain in their older contracts.

Please see the table below regarding what I am trying to achieve - with the appropriate 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 that I have that does not return 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 the result of the code (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 last contract does not have an expiration date, but there is an open contract.

.

+4
4

cross apply(), start_date, end_date, open_contracts 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

:

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 */
;

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

SQL-, - :

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

2 .
( " " ) , .
, .

+1

.

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

ROW_NUMBER() CTE

: http://rextester.com/HQVXF56741

In the code below, I changed the date format, which you might not need.

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
0
source

Source: https://habr.com/ru/post/1679728/

More articles:


All Articles