场景Scenarion DEMO001系统由某些用户从8月10日预订到11月8日.
The Scenarion is DEMO001 system is booked from 10-Aug to 11-Aug 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如果这几天其他用户尝试预订此系统.我们必须检查并传递0或1.
If Other user tries to book this system between these days . We have to check and pass 0 or 1.
我将得到一个输入参数
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我必须编写一个PLSQL/SQL代码以传递0,因为DEMO001介于这些开始数据和输入的结束日期之间,否则传递1.
I have to write a PLSQL/SQL code to pass 0 because DEMO001 is falling between these start data and end date of the input else pass 1.
请帮助.
推荐答案Oracle安装程序:
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;查询:
以绑定变量:start_date和:end_date作为输入:
With the bind variables :start_date and :end_date as the 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;输出:
对于输入:start_date = DATE '2016-08-08'和:end_date = DATE '2016-08-15':
SYSTEM CAN_BOOK ------- ---------- DEMO001 0 DEMO013 0 DEMO017 1对于输入:start_date = DATE '2016-09-06'和:end_date = DATE '2016-09-07':
SYSTEM CAN_BOOK ------- ---------- DEMO001 0 DEMO013 1 DEMO017 1对于输入:start_date = DATE '2016-08-08'和:end_date = DATE '2016-08-09':
SYSTEM CAN_BOOK ------- ---------- DEMO001 1 DEMO013 0 DEMO017 1更多推荐
检查“系统"是否为“系统".介于SQL中的两个日期之间
发布评论