Re: A complex SQL query

Would something like this work for you ? http://www.sqlfiddle.com/#!17/2e45eb/9

select
    user_id,
    max(start_date) as start_date,
    case when max(end_date) < max(start_date) then null else max(end_date) end as end_date
from
    employment
where
    employer = 'Micro'
group by
    user_id
;
On 6 Sep 2019, 14:31 +0200, jj08 <jj08(at)drivehq(dot)com>, wrote:
>  I hope someone can give me some pointers.
> Here is my table.
> +--------+----------+------------+-----------+
> | usr_id | employer | start_date | end_date  |
> +--------+----------+------------+-----------+
> | A      | Goo      | 201904     | -         |
> | A      | Micro    | 201704     | 201903    |
>
> | B      | Micro    | 201706     | -         |
> | B      | Goo      | 201012     | 201705    |
> | B      | Micro    | 201001     | 201011    |
> +--------+----------+------------+-----------+
>
> I am trying to list up people working for a company called "Micro".
> Some people work for a company multiple times, like user B.
> I only need one line per user, displaying only the latest affiliation date.
>
> For user_id "B", I could do Select user_id, MAX(start_date), end_date where employer = 'Micro',
> but that would fail to get record for user_id "A".
>
> If multiple records exist, I want to do MAX, but if only a single record exists, I don't need MAX.
> How do I do that?
> From the above data, I would like to see only two lines:
> | A      | Micro    | 201704     | 201903    |
> | B      | Micro    | 201706     | -         |
> Thank you.
>
> -------------------------
> Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and More.
> Drive Headquarters. Top quality services designed for business!
> Sign up free at: www.DriveHQ.com.