在范围之间选择记录并对其进行验证 [英] Selecting records in between range and apply validations on it

问题描述

我对于日期的开始日期和结束日期有两个字段(例如，对于政策而言).我必须选择这些日期之间的范围，并且不允许用户下次输入该范围之间的日期.
例如:
如果用户输入的开始日期为02/09/2010
结束日期为2011年7月31日
然后，下次他下次使用其政策再次输入开始日期时，该日期不能在先前的范围内..
1.
开始日期:2002年5月7日
结束日期:2004年9月12日

2.
开始日期:2005/10/12
结束日期:2007年3月16日

3.
开始日期:25/03/2010
结束日期:31/07/2011

假设这些是db..now中的三个记录，那么对于第4条记录，用户要选择的范围一定不能在以前的范围内..所以，我应该如何选择新范围???

I have two fields for dates start date & end date ( say for policy).. I have to select the range in between those dates and does not allow user to enter the date in between that range for the next time..
e.g.:
if user have entered start date as 02/09/2010
and end date as 31/07/2011
then when next time he used to enter start date again for his policy it must not be in previous range..
1.
start date: 05/07/2002
end date : 09/12/2004

2.
start date: 10/12/2005
end date : 16/03/2007

3.
start date: 25/03/2010
end date : 31/07/2011

suppose these are the three records in db..now for the 4th record the range which user going to select must not be in any of previous ranges..so how should i select new range???

推荐答案

最简单的解决方案是检查提议的范围是否与任何现有范围发生冲突，如果存在，则将其拒绝.像

The simplest solution is just to check whether the proposed range clashes with any of the existing ranges, and reject it if so. Something like

select id, startdate, enddate from ranges where (@startdate > startdate and @startdate < enddate) or (@enddate > startdate and @enddate < enddate)

@startdate和@enddate是建议的新范围的参数(从UI中拉出)，startdate和enddate是列名.如果返回任何行，则它们是与建议的新行冲突的范围，您应该构造一条错误消息，说明冲突是什么.

@startdate and @enddate are parameters (pulled from the UI) for the proposed new range, startdate and enddate are column names. If any rows are returned then they are ranges which clash with the proposed new one, and you should construct an error message explaining what the clashes are.

这篇关于在范围之间选择记录并对其进行验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！