0

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

2
  • Could you put together a fiddle with some mocked up data and your desired result?
    – bbaird
    Nov 18 at 15:05
  • @bbaird I have just added in the fiddle. Hope it will help you understand the scenario I have. Nov 18 at 16:09
0

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

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.