get min_start_date, max_end_date for same id

user_7122788 0
Viewed 40

I have a table something like below, It represents employees hire_date in the company. If the end date is null it means that these employees working with the company. For example in this table employee_id 2 has multiple records, which means that this employee was promoted inside the company.

emp_id  |start_date |end_date   |
--------------------------------
1       |1/1/2020   |12/12/2020 |
--------------------------------
2       |2/2/2020   |5/5/2020   |
--------------------------------
3       |9/9/2020   |null       |
--------------------------------
2       |5/5/2020   |1/1/2021   |
--------------------------------
2       |1/1/2021   |null       |
--------------------------------

What I want is to get the list of the employees with min(start_date) and max(end_date). if the end date is null then this null will be considered as a max date.

from above table expected output will be like below:

 emp_id |start_date |end_date   |
--------------------------------
1       |1/1/2020   |12/12/2020 |
--------------------------------
2       |2/2/2020   |null       |
--------------------------------
3       |9/9/2020   |null       |
--------------------------------

Currently, I am using multiple queries to achieve it. Please note that I am using postgresql.

postgresql sql
1 Answers

You can use distinct on for this:

select distinct on (emp_id) t.*
from t
order by emp_id, to_date desc nulls first;

distinct on is a very handy Postgres extension. If returns one row for the key values in parentheses. The row returned is the first one encountered based on the order by.

EDIT:

If you want to aggregate the date, but treat NULL as the maximum end date, then you can use:

select emp_id, min(start_date),
       (case when count(*) filter (where end_date is null) = 0
             then max(end_date)
        end) as end_date
from t
group by emp_id;
user_11440350
Related