问允许基于预订进行更新的方案-SQL

EN

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

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' ));

    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