How query which employee has been full-time employeed

user_9205369 0
Viewed 70

I have the following MySQL table:

+----+---------+----------------+------------+
| id | user_id | employment_type| date       |
+----+---------+----------------+------------+
|  1 |       9 | full-time      | 2013-01-01 |
|  2 |       9 | half-time      | 2013-05-10 |
|  3 |       9 | full-time      | 2013-12-01 |
|  4 |     248 | intern         | 2015-01-01 |
|  5 |     248 | full-time      | 2018-10-10 |
|  6 |      58 | half-time      | 2020-10-10 |
|  7 |     248 | NULL           | 2021-01-01 |
+----+---------+----------------+------------+

I want to query, for example, which employees were full-time employed on 2014-01-01. Which SQL query I need to pass to get the correct result?

In this case, the result will be an employee with user_id=9;

Is this table properly structured to be possible to get such a result?

select mysql sql
2 Answers

If your version of MySql is 8.0+ you can do it with FIRST_VALUE() window function:

SELECT DISTINCT user_id
FROM (
  SELECT user_id,
         FIRST_VALUE(employment_type) OVER (PARTITION BY user_id ORDER BY date DESC) last_type
  FROM tablename
  WHERE date <= '2014-01-01'
) t
WHERE last_type = 'full-time'

For previous versions of MySql you can do it with NOT EXISTS:

SELECT t1.user_id
FROM tablename t1
WHERE t1.date <= '2014-01-01' AND t1.employment_type = 'full-time'
  AND NOT EXISTS (
    SELECT 1 
    FROM tablename t2 
    WHERE t2.user_id = t1.user_id AND t2.date BETWEEN t1.date AND '2014-01-01' 
      AND COALESCE(t2.employment_type, '') <> t1.employment_type
  )

See the demo.
Results:

| user_id |
| ------- |
|       9 |
user_104988280

You want the most recent record on or before that date. I would use row_number():

select t.*
from (select t.*,
             row_number() over (partition by user_id order by date desc) as seqnum
      from t
      where date <= '2014-01-01'
     ) t
where seqnum = 1 and employment_type = 'full_time';

A fun method that just uses group by is:

select t.user_id
from t
where t.date <= '2014-01-01'
group by t.user_id
having max(date) = max(case when employment_type = 'full_time' then date end);

This checks that the maximum date -- before the cutoff -- is the same as the maximum date for 'full-time'.

user_11440350
Related