How to get available worker from a roster database?

Posted on

Question :

I am using Postgres, and I am working on a database for a roster system like this:

drop table if exists sessions;
create table if not exists sessions(
    id int GENERATED ALWAYS AS IDENTITY,
    name varchar(256) not null,
    start_date timestamptz not null,
    end_date timestamptz not null,
    primary key(id)
);

drop table if exists workers;
create table if not exists workers(
    id int GENERATED ALWAYS AS IDENTITY,
    name varchar(256) not null,
    primary key(id)
);

drop table if exists sessions_workers;
create table if not exists sessions_workers(
    id int GENERATED ALWAYS AS IDENTITY,
    session_id int not null,
    worker_id int not null,
    primary key(id),
    foreign key(session_id) references sessions(id) ON DELETE CASCADE,
    foreign key(worker_id) references workers(id) ON DELETE CASCADE
);

So by associating the worker in the junction table with that session, the worker will attend that session. But what I can figure out is, how can I know if a worker is not available, even if it has not attached to the session due to overlapped timeslot?

For an example the following are the seed.

insert into sessions(name, start_date, end_date) values 
(
    'Session A', '2021-10-19 09:00:00+02', '2021-10-19 10:00:00+02'
), 
(
    'Session B', '2021-10-19 09:00:00+02', '2021-10-19 11:00:00+02'
); 

insert into workers(name) values 
('tom'),
('john');

insert into sessions_workers(session_id, worker_id) values (1, 1), (2, 2);

john will not be available for Session A, even though he has not associated with that session, as he should be occupied by Session B. What kind of query can I use to figure that out?

EDIT

https://www.db-fiddle.com/f/c4XVdberRTitKmvbGT4Cif/2

Answer :

I can’t believe that I am able to pull this off by myself. SQL is soooooo hard. Turn out I have to use range to deal with this kind of data.

select * from sessions_workers
join sessions on sessions.id = sessions_workers.session_id
right join workers on sessions_workers.worker_id = workers.id
where (sessions.id <> 1 or sessions.id is null)
and not sessions.duration && (select duration from sessions where sessions.id = 1);

https://www.db-fiddle.com/f/fk5knWwCU3qMafuCJm8BfS/2

Leave a Reply

Your email address will not be published. Required fields are marked *