方案是DEMO001系统由用户在8月10日至8月8日预订.
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
假设我得到一个输入参数
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
如果用户尝试通过延长或推迟开始日期或结束日期来更新任何此系统,如果在这些天之间没有其他人预订,我将不得不说0或1.
这是此方案的扩展
Checking if the “system” falls between two dates in SQL
我尝试修改它给出的建议,但无法正确使用它.请建议.
解决方法
尝试：
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
