Checking for a "system" between two dates in SQL

Question

Scenarion - DEMO001 system, registered from August 10 to August 11 by some user.

START_DATE END DATE SYSTEM 2016-08-10 2016-08-11 DEMO001 2016-09-05 2016-09-08 DEMO001 2016-08-08 2016-08-11 DEMO013 2016-08-16 2016-08-18 DEMO017 

If another user is trying to reserve this system these days. We must check and pass 0 or 1.

I will get the input parameter

 1) start_date as 2016-08-08 and 2016-08-15. 2) 2016-09-06 and 2016-09-07 3) 2016-08-08 and 2016-08-09 

I need to write PLSQL / SQL code to pass 0 because DEMO001 falls between this initial data and the end date of input else pass 1.

Please help.

Answer 1

Oracle setup :

 CREATE TABLE table_name ( START_DATE, END_DATE, SYSTEM ) AS SELECT DATE '2016-08-10', DATE '2016-08-11', 'DEMO001' FROM DUAL UNION ALL SELECT DATE '2016-09-05', DATE '2016-09-08', 'DEMO001' FROM DUAL UNION ALL SELECT DATE '2016-08-08', DATE '2016-08-11', 'DEMO013' FROM DUAL UNION ALL SELECT DATE '2016-08-16', DATE '2016-08-18', 'DEMO017' FROM DUAL; 

Query

With bind variables :start_date and :end_date as inputs:

 SELECT system, MIN( CASE WHEN :end_date < start_date OR :start_date > end_date THEN 1 ELSE 0 END ) AS can_book FROM table_name GROUP BY system; 

Exit

For inputs :start_date = DATE '2016-08-08' and :end_date = DATE '2016-08-15' :

 SYSTEM CAN_BOOK ------- ---------- DEMO001 0 DEMO013 0 DEMO017 1 

For inputs :start_date = DATE '2016-09-06' and :end_date = DATE '2016-09-07' :

 SYSTEM CAN_BOOK ------- ---------- DEMO001 0 DEMO013 1 DEMO017 1 

For inputs :start_date = DATE '2016-08-08' and :end_date = DATE '2016-08-09' :

 SYSTEM CAN_BOOK ------- ---------- DEMO001 1 DEMO013 0 DEMO017 1 

Answer 2

According to your requirements, you want to prohibit reservations if the start or end date is between this entry. You can simply use the CASE expression to do this. I hard coded 2016-08-08 and 2016-08-15 as the start and end dates that you use.

 SELECT SYSTEM, CASE WHEN (DATE '2016-09-06' < START_DATE AND DATE '2016-09-07' < START_DATE) OR (DATE '2016-09-06' > END_DATE AND DATE '2016-09-07' > END_DATE) THEN 1 ELSE 0 END AS allowBooking FROM yourTable 

Here is a link to an SQL script showing a query in action for an input range from 2016-08-08 to 2016-08-09 . This clearly shows that all orders are possible, with the exception of DEMO013 . I used MySQL, but the logic should not change when moving from MySQL to Oracle or vice versa.

SQLFiddle