How to make trigger function to make daily entering-exiting report?

Question

I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in.

CREATE TABLE daily_report (
        id serial,
        worker_id integer,
        in_or_out integer,
        the_time time,
        the_date date
        );

I assigned "in" status to 1, "out" to 0. I need a query that shows when the worker first enter the building and when he/she exits it in the specific day.(notice some workers may enter the building and exit next day, like night guards) . Like : Select min(time), max (time) group by date........ I tried to join table to itself but cant get a result. Trigger function also will be ok that records daily report in a new table like worker with id 3 enters 08:00 am, exits 18 00 pm in '2016-01-01' for example

please help me to do that.

Answer 1

Let's assume every "in" is followed by an "out" event. In that case you can do:

SELECT worker_id, 
    the_date as in_date, 
    the_time as in_time,
    lead(the_date) over w as out_date, 
    lead(the_time) over w as out_time,
    -- Check if it is really an out event
    (lead(in_or_out) over w) = 0 as out
FROM daily_report
-- Filter for in events
WHERE in_or_out = 1 AND worker_id = [your_worker_id]
-- specify the window function
WINDOW w AS (order by the_date, the_time)
GROUP BY worker_id;

You didn't write which PG version do you have. A better approach is to have LATERAL JOINs (available since 9.3):

SELECT worker_id, i.the_date, i.the_time, o.the_date, o.the_time
FROM daily_report i
LEFT JOIN LATERAL (
    SELECT r.the_date, r.the_time 
    FROM daily_report r 
    WHERE 
        in_or_out = 0 AND r.worker_id = i.worker_id
        (
            (r.the_date = i.the_date and r.the_time > i.the_time) OR 
            (r.the_date > i.the_date)
        )
    ORDER BY 1, 2
    LIMIT 1
) o ON true
WHERE in_or_out = 1 AND worker_id = [your_worker_id]

This is a more generic query and will give you back better results if the data is somehow not completely correct.