CODEWITHSUNDEEP
sqlsql-view
Bradmcker
Bradmcker

Reputation: 61

Time period SQL view

I am trying to create a SQL view on two tables I'm working with:

CREATE TABLE availability
(
    doctor varchar(20) NOT NULL,
    avl_date date NOT NULL,
    avl_start time NOT NULL,
    avl_end time NOT NULL,

    CONSTRAINT pk_availability PRIMARY KEY (doctor, avl_date)
);

And

CREATE TABLE appointments
(
    patient varchar(20) NOT NULL,
    doctor varchar(20) NOT NULL,
    apt_date date NOT NULL,
    apt_start time NOT NULL,
    apt_end time NOT NULL,

    CONSTRAINT pk_appointments PRIMARY KEY (patient, apt_date)
);

The view I am trying to create lists all maximal time periods (apt date, apt start, apt end) during which no further appointments are possible (consider doctors’ availability as well).

Any help is greatly appreciated, thanks.

Upvotes: 1

Views: 168

Answers (1)

Zeeshan Arif
Zeeshan Arif

Reputation: 499

this should work if the appointment time-duration is constant. You can optimize it by re-writing with inner join instead of minus. Also, remember to put availability table above minus.

select doctor
, avl_date
, avl_start
, avl_end
from availability
minus
select doctor
, apt_date
, apt_start
, apt_end
from appointments

Upvotes: 1

Related Questions