Oracle SQL Query来检查活动的有效日期范围？

问题描述：

我有一个称为Activity与像activity_id和activity_date列的表。Oracle SQL Query来检查活动的有效日期范围？

考虑像下面的活动表中的数据，

activity_id activity_date  
1    1st June 
2    1st July 
3    1st August 
4    1st September 
5    1st October

现在我想更改活动3日，但是因为有我不能日期更改为小于1st July或超过1st September在这些日期已经有一些其他活动。

的活动3的唯一有效日期是30th August2nd July之间。

同样，对于活动1，新的有效日期是1st July之前的任何日期。

同样，对于活动5，新的有效日期范围从2nd September在未来的任何日期作为其最后的活动。

如果新日期不在范围内，我需要在前端为用户提供验证消息。 输入到查询将活动ID和新活动的日期。

下面是DDL脚本中使用类似下面的查询

CREATE TABLE "HEADCOUNT"."ACTIVITY" 
( "ACTIVITY_ID" NUMBER(*,0) NOT NULL, 
    "ACTIVITY_DATE" DATE 
); 

Insert into "HEADCOUNT"."ACTIVITY" (ACTIVITY_ID,ACTIVITY_DATE) values (1,'01-06-2012'); 
Insert into "HEADCOUNT"."ACTIVITY" (ACTIVITY_ID,ACTIVITY_DATE) values (2,'01-07-2012'); 
Insert into "HEADCOUNT"."ACTIVITY" (ACTIVITY_ID,ACTIVITY_DATE) values (3,'01-08-2012'); 
Insert into "HEADCOUNT"."ACTIVITY" (ACTIVITY_ID,ACTIVITY_DATE) values (4,'01-09-2012'); 
Insert into "HEADCOUNT"."ACTIVITY" (ACTIVITY_ID,ACTIVITY_DATE) values (5,'01-10-2012');

如何更改日期？它可以通过数据库触发器来实现，或者根据更改值的方法进行前端验证。 – Ollie 2012-07-30 13:27:08

@Ollie。我需要在前端添加验证。我们没有使用触发器。 SQl查询用于将活动更新为新的活动日期。 – ashishjmeshram 2012-07-30 13:30:42

你的麻烦是验证SQL插入最好通过触发器来完成，除非你使用一些前端环境在输入到数据库之前验证输入。您的其他选项（不是触发器）是通过在插入记录之前执行验证的存储过程进行更新，并在验证失败时返回错误消息。 – Ollie 2012-07-30 13:41:49

答

这会发现每一行的日期范围：

SELECT activity_id, activity_date 
     ,NVL(LAG(activity_date) OVER(ORDER BY activity_id) 
      ,TO_DATE('1900-01-01', 'YYYY-MM-DD') 
     ) AS previous_date 
     ,NVL(LEAD(activity_date) OVER(ORDER BY activity_id) 
      ,TO_DATE('2100-01-01', 'YYYY-MM-DD') 
     ) AS next_date 
    FROM activity 
    ORDER BY activity_id

结果：

     ACTIVITY_ID ACTIVITY_DATE PREVIOUS_DATE NEXT_DATE 
---------------------------------- ------------- ------------- --------- 
           1 01-JUN-12  01-JAN-00  01-JUL-12 
           2 01-JUL-12  01-JUN-12  01-AUG-12 
           3 01-AUG-12  01-JUL-12  01-SEP-12 
           4 01-SEP-12  01-AUG-12  01-OCT-12 
           5 01-OCT-12  01-SEP-12  01-JAN-00

验证将被用于给定ID：

“输入日期” > previous_date AND“输入日期”< next_date

日期范围基于由activity_id排序的先前和后续记录。虽然排序可能应该是由activity_date确定的。使用LAG和LEAD也会在activity_ids中留下空白。

答

查找日期限制（不断变化的活动ID替换@param_id）：

SELECT activity_id, activity_date 
FROM activity 
WHERE activity_id = @param_id-1 
    OR activity_id = @param_id+1

这个查询将返回最多两个结果，但对于第一次和最后一次活动，只有一次会被退回。所以，你应该阅读前端结果，并决定该怎么做：

指定开始限制：结果与ID @param_id-1指定开始日期的限制。如果没有带该ID的结果，则这是第一个活动，并且对于开始日期没有限制。
指定结束限制：结果与标识@param_id+1指定结束日期限制。如果没有使用此ID的结果，则这是最后一次活动，并且没有结束日期的限制。
执行或发出警告：如果新日期在该范围内，请执行更改。否则，警告用户。

Oracle SQL Query来检查活动的有效日期范围？

相关推荐