Search code examples
sqlpostgresqlwindow-functions

Reduce consecutive rows with the same value in a column to a single row

I am trying to create a biometric attendance system that receives data from a biometric device.

The structure of the attendance table received from the device looks something like this.

sample_table

The table originally has a lot of data with more than one emp_no, but I created a stored procedure that extracts details of one employee on a specific date as seen above. The challenge that is facing right now is that, I need to analyze this table and restructure it ( recreate another table ) so that it has alternating check-ins and checkouts ( each checkin must be followed by a checkout and vice versa ) and for consecutive check-ins, I should take the earlier one while for consecutive check-outs, I should take the latest one. Any ideas on how to go about this will be very much appreciated. Thank you.

Solution

  • Use the window functions lag() and lead():

    select emp_id, att_date, att_time, status
from (
    select 
        emp_id, att_date, att_time, status, 
        case 
            when status = 'checkin' then lag(status) over w is distinct from 'checkin'
            else lead(status) over w is distinct from 'checkout'
        end as visible
    from my_table
    window w as (partition by emp_id, att_date order by att_time)
    ) s
where visible

    Db<>fiddle.