1

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.

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.

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.