I'm having a hard time describing my problem or finding it via Google.

I want to be able to group rows based on location and employee, but the group should be split if a row with the same location occurs.

Basically a way to determine shifts. If another employee logs a row at the same location then the last created_at should be the end date of the previous employee.

Starting from this (employee_logs):

location | employee | created_at
A        | Bart     | 2015-09-01 09:00:00
B        | Homer    | 2015-09-01 09:05:00
A        | Bart     | 2015-09-01 09:30:00
A        | Bart     | 2015-09-01 10:00:00
A        | Homer    | 2015-09-01 10:05:00
A        | Homer    | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00
A        | Bart     | 2015-09-01 14:00:00

The desired result is (shifts):

location | employee | start               | end
A        | Bart     | 2015-09-01 09:00:00 | 2015-09-01 10:00:00
B        | Homer    | 2015-09-01 09:05:00 | 2015-09-01 09:05:00 
A        | Homer    | 2015-09-01 10:05:00 | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00 | 2015-09-01 14:00:00

The hard part is that the sequence should be interrupted when another person creates a log at the same location. I can't seem to solve this with the usual techniques.


1 Answer 1

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts
   SELECT location, employee, created_at
        , row_number() OVER (PARTITION BY location ORDER BY created_at)
        - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp
   FROM   employee_logs
   ) sub
GROUP  BY location, employee, grp
ORDER  BY min(created_at), max(created_at);

This interrupts a sequence when a different employee logs at the same location. It does not interrupt when the same employee logs at a different location (which might also make sense, but you didn't mention that).

In the subquery, have an running number over all rows for the same location and subtract the running number over rows for the same location with the same employee. The result grp is a number without meaning, that denotes each group of rows forming a sequence together.

Aggregate in the outer SELECT. It is important to remember that grp is only unique per (location, employee), so you have to group by all three columns.

SQL Fiddle.

Related, with ample explanation:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.