Extract employee record based on certain criteria

Question

I have a database of employees with their employment history in organization.

Sample Data -

+----+----------+------------+
| ID |   Date   |   Event    |
+----+----------+------------+
|  1 | 20190807 | Hired      |
|  1 | 20191209 | Promoted   |
|  1 | 20200415 | Terminated |
|  2 | 20180901 | Hired      |
|  2 | 20191231 | Terminated |
|  3 | 20180505 | Hired      |
|  3 | 20190630 | Promoted   |
+----+----------+------------+

I want to extract the list of employees who were terminated after promotion. In above example, the query should return ID 1.

I am using SSMS 17 if that helps.

It's not the version of SSMS that matters but rather the version of SQL Server to which it connects, as it's the Server that executes the queries. — underscore_d, Jul 17 '20 at 10:38

score 0 · Answer 1 · answered Jul 17 '20 at 10:35

0

You can try using lag()

DEMO

select distinct ID from
(
select *,lag(event) over(partition by id order by dateval) as prevval
from t
)A where prevval='Promoted'

answered Jul 17 '20 at 10:35

Fahmi

37,315
5
22
31

score 0 · Answer 2 · answered Jul 17 '20 at 10:42

If you want immediately after, then you would use lag(). If you want any time after, then you can use aggregation:

select id
from t
group by id
having max(case when event = 'Promoted' then dateval end) < max(case when event = 'Terminated' then dateval end);

Using lag(), the code looks like:

select id
from (select t.*, lag(event) over (partition by id order by dateval) as prev_event
      from t
     ) t
where prev_event = 'Promoted' and event = 'Terminated';

score 0 · Answer 3 · answered Jul 17 '20 at 11:01

A simple exists check could also solve this simple requirement.

DEMO

select * from table1 a
where event='Terminated'
and exists(select 1 from table1 b where a.ID = b.ID  and event='Promoted');

output:

ID  date1       event
1   20191209    Terminated

We can even compare event date in correlated sub-query as shown in DEMO link.

Extract employee record based on certain criteria

3 Answers3