Not sure how to set up the conditional (or if it requires operations; fresh to SQL) to find vacant unit.

Units are accompanied by start and end date of lease.

INSERT INTO lease (unit, s_date, e_date) VALUES (1, '20200101','20200530');
INSERT INTO lease (unit, s_date, e_date) VALUES (2, '20200701','20201231');
INSERT INTO lease (unit, s_date, e_date) VALUES (3, '20200201','20210501');
INSERT INTO lease (unit, s_date, e_date) VALUES (4, '20200615','20201225');

i.e. between 2020/06/01-30, unit # 1 & 2 are vacant and should show.

However,

SELECT * FROM lease WHERE (s_date NOT BETWEEN '20200601' AND '20200630') AND (e_date NOT BETWEEN  '20200601' AND '20200630');

results in showing unit 3 as well

Thanks in advance!

CodePudding user response：

Your table definition is bad:

• Never use a data type other than date for dates. Neither strings nor numbers are appropriate.

• Use daterange to store date intervals.

So this should be:

CREATE TABLE lease (
   unit bigint NOT NULL,
   leased daterange NOT NULL
);

INSERT INTO lease VALUES (1, '[2020-01-01,2020-05-30]');
...

Then your query would be:

SELECT unit
FROM lease
GROUP BY unit
HAVING NOT range_agg(leased) && '[2020-06-01,2020-06-30]';

This requires the multirange support from PostgreSQL v14. && is the “overlaps” operator.

With PostgreSQL v13 and below, you can use:

   SELECT unit
   FROM lease
EXCEPT
   SELECT unit
   FROM lease
   WHERE leased && '[2020-06-01,2020-06-30]';