kkris77 kkris77 - 7 months ago 22
SQL Question

SQL WHERE MAX(date) within GROUP BY

I've created a SQL Fiddle (http://sqlfiddle.com/#!9/e0536/1) with similar data I've got at work (there are actually more columns in the table). Table contains employment details. An employee can have more than one record in the table (couple of fixed-term contracts) as well as different employee_ID (change from 'tixxxxx' into 'pixxxxx'). The PESEL number is the unique personal identification number. ID for past contract can be higher than for actual one as the table is populated with data every day as an extract based on HR data.

What I need to get is:


  • at least up-to-date employee_ID (the line where expirationdate is max)

  • a whole line with all columns for up-to-date employee_ID

  • best if I could get a whole line for up-to-date employee_ID including the very first startdate (important if employee had more than one contract)



It's been some time since I used SQL every day so I'd appreciate any help here. I was thinkig of some nested queries with group by clause, but I never understood well correlated subqueries.

Expected result:

ID Employee_ID PESEL StartDate ExpirationDate
----------- ----------- ----------- ---------- --------------
1 pi39764 1111 2014-01-01 2016-06-01
2 pi12986 1234 2015-12-01 2099-12-31
5 pi12345 4321 2015-02-01 2099-12-31


where the startdate is the very first startdate.

Answer

so try this:

SELECT ID, Employee_ID, PESEL, first_startdate AS StartDate, ExpirationDate
FROM (
    SELECT e.*
    , LEAD (Employee_ID) OVER (PARTITION BY PESEL ORDER BY startdate) actual_Employee_ID
    , MIN(startdate) OVER (PARTITION BY PESEL) first_startdate
    FROM Employees AS e) AS x
WHERE actual_Employee_ID is null

then you get:

| ID | Employee_ID | PESEL |  StartDate | ExpirationDate |
|----|-------------|-------|------------|----------------|
|  1 |     pi39764 |  1111 | 2014-01-01 |     2016-06-01 |
|  2 |     pi12986 |  1234 | 2015-12-01 |     2099-12-31 |
|  5 |     pi12345 |  4321 | 2015-02-01 |     2099-12-31 |

http://sqlfiddle.com/#!3/e0536/7