CODEWITHSUNDEEP
sqldatabasepostgresql
Marsilinou Zaky
Marsilinou Zaky

Reputation: 1047

Employee scheduling system

I have employees table and I'm trying to query only employees available in timestamp range. 

select 
    emp_fn_name, emp_fn_name,
    TO_TIMESTAMP(e.start_time, 'HH12:MIAM,PM')::TIME,
    TO_TIMESTAMP(e.end_time, 'HH12:MIAM,PM')::TIME
from 
    employees e
inner join 
    schedule s on emp_id = e.id
where 
    TO_TIMESTAMP(s.start_time, 'HH12:MIAM,PM')::TIME
        not BETWEEN TO_TIMESTAMP('2:00PM', 'HH12:MIAM,PM')::TIME
                AND TO_TIMESTAMP('03:00PM', 'HH12:MIAM,PM')::TIME

Sample data for schedule table:

start   end     days    emp_id  emp_fn
2:00    3:00PM  TuTh    38      e1
2:00    3:00PM  TuTh    154     e2
4:00    5:00AM  TuTh    154     e3
6:00    7:00AM    MW    154     e1
7:00    8:00AM    MW    154     e4

If I'm looking for someone to work on TuTh from 2:00 to 3:00pm, I should get emp3 and emp4 since they're not busy at the specified time range.

Thank you.

Upvotes: 0

Views: 138

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270533

I think you want:

select emp_fn_name, emp_fn_name
from employees e 
where not exists (select 1
                  from schedule s
                  where s.emp_id = e.id and
                        s.start < '03:00PM'::time and
                        s.end > '02:00PM'::time
                 );

Upvotes: 2

Related Questions