Oracle plsql check dates between two sides-CodePudding

I don't know what use in below situation while loop or any other?

In table tbl_1 I have 3 column:

id  date_from     date_to
---------------------------
1   01.09.2021.  02.09.2021.
2   01.09.2021.  01.09.2021.
3   01.09.2021.  03.09.2021.

On other side (client) I have date range eg. date_from_client (01.09.2021.) and date_to_client (02.09.2021.)

How check is all dates between date_from_client and date_to_client are located in tbl_1.

Return true if not exists or return false if exists.

In above example final resaut is FALSE.

For below example final resaut is TRUE. (Row 3 03.09.2021. is different between date range date_from_client (01.09.2021.) and date_to_client (02.09.2021.)

id  date_from     date_to
---------------------------
1   01.09.2021.  02.09.2021.
2   01.09.2021.  01.09.2021.
3   03.09.2021.  03.09.2021.

in my databse date format is: dd.mm.yyyy.

CodePudding user response：

Something like this? JOIN condition says which rows to look at. If MIN value of date comparison is 0, return false; otherwise, return true.

First sample data:

SQL> WITH
  2     client (date_from_client, date_to_client)
  3     AS
  4        (SELECT DATE '2021-09-01', DATE '2021-09-02' FROM DUAL),
  5     tbl_1 (id, date_from, date_to)
  6     AS
  7        (SELECT 1, DATE '2021-09-01', DATE '2021-09-02' FROM DUAL
  8         UNION ALL
  9         SELECT 2, DATE '2021-09-01', DATE '2021-09-01' FROM DUAL
 10         UNION ALL
 11         SELECT 3, DATE '2021-09-01', DATE '2021-09-03' FROM DUAL)
 12  SELECT CASE
 13            WHEN MIN (CASE
 14                         WHEN     a.date_from >= b.date_from_client
 15                              AND a.date_to <= b.date_to_client
 16                         THEN
 17                            1
 18                         ELSE
 19                            0
 20                      END) = 1
 21            THEN
 22               'TRUE'
 23            ELSE
 24               'FALSE'
 25         END cb_date
 26    FROM tbl_1 a
 27         JOIN client b
 28            ON a.date_from BETWEEN b.date_from_client AND b.date_to_client;

CB_DA
-----
FALSE

Second sample data:

SQL> WITH
  2     client (date_from_client, date_to_client)
  3     AS
  4        (SELECT DATE '2021-09-01', DATE '2021-09-02' FROM DUAL),
  5     tbl_1 (id, date_from, date_to)
  6     AS
  7        (SELECT 1, DATE '2021-09-01', DATE '2021-09-02' FROM DUAL
  8         UNION ALL
  9         SELECT 2, DATE '2021-09-01', DATE '2021-09-01' FROM DUAL
 10         UNION ALL
 11         SELECT 3, DATE '2021-09-03', DATE '2021-09-03' FROM DUAL)
 12  SELECT CASE
 13            WHEN MIN (CASE
 14                         WHEN     a.date_from >= b.date_from_client
 15                              AND a.date_to <= b.date_to_client
 16                         THEN
 17                            1
 18                         ELSE
 19                            0
 20                      END) = 1
 21            THEN
 22               'TRUE'
 23            ELSE
 24               'FALSE'
 25         END cb_date
 26    FROM tbl_1 a
 27         JOIN client b
 28            ON a.date_from BETWEEN b.date_from_client AND b.date_to_client;

CB_DA
-----
TRUE

SQL>