Scenario to allow update based on booking-SQL

Question

The Scenario is DEMO001 system is booked from 10-Aug to 11-Aug by the 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

Say if I get an input parameter

1) start date as 2016-08-08  and end date as 2016-08-11 I can allow 
2) start date as 2016-08-11  and end date as 2016-09-08 I cannot allow
3) start date as 2016-08-10   and end date as 2016-08-15 I can allow
3) start date as 2016-08-10   and end date as 2016-09-06 I cannot allow

If the user tries to update any of this system by extending or preponing the start or end date if no others booked between these days I will have to say 0 or 1 .

This is the extension to this scenario

Checking if the "system" falls between two dates in SQL

I tried modifying it the suggestion given there but not able to get it correctly. Kindly suggest.

Answer 1

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 query
SELECT 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_allowed
FROM dates d
LEFT 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_date
ORDER BY 1

Answer 2

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 anyway
COMMIT;

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"

Answer 3

Full credit to @kordirko here, but just to make you understand with your familiar output model (as given by @MTO)

Bookings table:

CREATE TABLE table_name ( START_DATE, END_DATE, SYSTEMS ) 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 systems, start_date, end_date

SELECT :systems, :start_date, :end_date, 
       CASE WHEN COUNT(*) > 1 
            THEN 'I cannot Allow' ELSE 'I can Allow' 
        END result
FROM bookings t1 
WHERE ( :start_date <= t1.end_date) AND (:end_date >= t1.start_date ) 
    AND t1.systems = :systems
GROUP BY :start_date, :end_date;

Output:

1) DEMO001  2016-08-08  2016-08-11  -> I can Allow
2) DEMO001  2016-08-11  2016-09-08  -> I cannot Allow
3) DEMO001  2016-08-10  2016-08-15  -> I can Allow
4) DEMO001  2016-08-10  2016-09-06  -> I cannot Allow

Answer 4

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  AnotherBookingDatesOfSystem

It works for all given scenarios.