Scenario to allow update based on booking-SQL
Try:
WITH dates AS ( -- input data (ranges) SELECT date '2016-08-08' as start_date, date '2016-08-11' as end_date from dual union all SELECT date '2016-08-11', date '2016-09-08' from dual union all SELECT date '2016-08-10', date '2016-08-15' from dual union all SELECT date '2016-08-10', date '2016-09-06' from dual)-- the querySELECT d.start_date, d.end_date, CASE WHEN count(*) > 1 THEN 'Disallow' ELSE 'Allow' -- change the above line to => THEN 0 ELSE 1 <= if you prefer numbers END is_allowedFROM dates dLEFT JOIN table1 t1 -- table1 holds booking data, eg DEMO0001 etc.ON (d.Start_date <= t1.end_date) and (d.end_date >= t1.start_date ) AND t1.system = 'DEMO001'GROUP BY d.start_date, d.end_dateORDER BY 1
If I understand your question correctly, you are looking for a generic solution to distinguish if periods for a resource overlap in time.
Assuming those first four example lines are columns in a table named BOOKING, and you want to test a new date for the first booking, you can do this with queries like this:
CREATE TABLE booking( system_name VARCHAR2( 10 ) , start_date DATE , end_date DATE );INSERT INTO booking( system_name, start_date, end_date ) VALUES ( 'DEMO001' , TO_DATE( '2016-09-05', 'YYYY-MM-DD' ) , TO_DATE( '2016-09-08', 'YYYY-MM-DD' ) );-- You only need this record, as you need to filter on the system name anywayCOMMIT;SELECT CASE COUNT( 1 ) WHEN 0 THEN 'I can allow' ELSE 'I cannot allow' END AS outcome FROM DUAL WHERE EXISTS (SELECT 1 FROM booking old WHERE old.system_name = 'DEMO001' AND old.end_date > TO_DATE( '2016-08-08', 'YYYY-MM-DD' ) AND old.start_date < TO_DATE( '2016-08-08', 'YYYY-MM-DD' ));SELECT CASE COUNT( 1 ) WHEN 0 THEN 'I can allow' ELSE 'I cannot allow' END AS outcome FROM DUAL WHERE EXISTS (SELECT 1 FROM booking old WHERE old.system_name = 'DEMO001' AND old.end_date > TO_DATE( '2016-08-11', 'YYYY-MM-DD' ) AND old.start_date < TO_DATE( '2016-09-08', 'YYYY-MM-DD' ));SELECT CASE COUNT( 1 ) WHEN 0 THEN 'I can allow' ELSE 'I cannot allow' END AS outcome FROM DUAL WHERE EXISTS (SELECT 1 FROM booking old WHERE old.system_name = 'DEMO001' AND old.end_date > TO_DATE( '2016-08-10', 'YYYY-MM-DD' ) AND old.start_date < TO_DATE( '2016-08-15', 'YYYY-MM-DD' ));SELECT CASE COUNT( 1 ) WHEN 0 THEN 'I can allow' ELSE 'I cannot allow' END AS outcome FROM DUAL WHERE EXISTS (SELECT 1 FROM booking old WHERE old.system_name = 'DEMO001' AND old.end_date > TO_DATE( '2016-08-10', 'YYYY-MM-DD' ) AND old.start_date < TO_DATE( '2016-09-06', 'YYYY-MM-DD' ));Of course the CASE Statement is just there to make the test outcome visually clear. If you want 0 and 1 for the opposite outcomes, just make it a "NOT EXIST"
According to my understanding, you need to update existing booking dates of system only if no other date's clashes.
Please try below code, May it works from you.
CREATE TABLE bookings (BookingId INT IDENTITY(1,1), StartDate Date, EndDate DATE, [SYSTEM] varchar(64)); INSERT INTO bookings (StartDate, EndDate, [SYSTEM]) VALUES ('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');
DECLARE @ExistingBookingId INT = 1 ,@NewStartDate DATE = '2016-08-10' ,@NewEndDate DATE = '2016-09-06'; DECLARE @SystemCorrespondingToBookingId VARCHAR(64); SELECT @SystemCorrespondingToBookingId = [System] FROM bookings WHERE bookingId = @ExistingBookingId ;WITH AnotherBookingDatesOfSystem (StartDt, EndDt) AS ( SELECT StartDate, EndDate FROM Bookings WHERE [System] = @SystemCorrespondingToBookingId AND BookingId <> @ExistingBookingId ) SELECT ISNULL(MIN( CASE WHEN @NewEndDate < StartDt OR @NewStartDate > EndDt THEN 1 ELSE 0 END ), 1) AS can_book FROM AnotherBookingDatesOfSystemIt works for all given scenarios.