Between the time in SQL Server and ASP.NET-CodePudding

I am storing STARTTIME and ENDTIME in a SQL Server table by using ASP.NET webform.

 timeTableID       startTimeVal      endTimeVal
-------------------------------------------------
    1       07:30:00.0000000    08:00:00.0000000
    2       08:00:00.0000000    08:30:00.0000000
    3       08:15:00.0000000    08:25:00.0000000

Now, as you can see in the row with timeTableID = 2, the inserted time value is 8:00 & 8:30 AM,

But in the row with timeTableID = 3, the inserted time value is 8:15 & 8:25 AM,

I don't want this because its within value of row 2.

My problem is, the next entry should not between the already inserted time.

My attempt:

select timeTableID 
from timetableTbl 
where dayVal = 'MONDAY' 
  and StartTimeVal between '"   startTimeTxt.Text   "' and '"   endTimeTxt.Text   "' and endTimeVal between '"   startTimeTxt.Text   "' and '"   endTimeTxt.Text   "'

Here startTimeTxt is the textbox for startTimeVal and endTimeTxt is the textbox for endTimeVal.

Please help me with this

CodePudding user response：

It sounds like you are trying to detect and avoid overlapping time intervals prior to insert. The way to test for this is not obvious, but the following works: Given two time intervals [start1, end1) and [start2, end2), they overlap iff (start1 < end2 AND start2 < end1).

Note that this specifically allows for adjacent intervals where start1 = end2 or start2 = end1, as is common for continuous ranges and reflects the condition for records 1 and 2 above. (If we were dealing with discrete values and inclusive ranges, the tests would use <= instead of <.)

This also assumes that you want to avoid any overlap, not just the case where the new interval is entirely contained within an existing interval. For example, given records 1 & 2 above, you would like to also prohibit intervals 7:15am-7:45am (overlaps start of record 1), 8:15am-8:45am (overlaps end of record 2), and 6:00am-12:00pm (completely overlaps both).

In this case, the check for a conflicting interval would be something like

select timeTableID 
from timetableTbl 
where dayVal = @day
and StartTimeVal < @endTime
and EndTimeVal > @startTime  -- Same as @startTime < EndTimeVal

Where @day, @startTime, and @endTime are parameters derived from validated user input.

CodePudding user response：

to list those rows:

SELECT t2.* FROM tablename AS t
JOIN tablename AS t2 
    ON t2.timeTableID > t.timeTableID
    AND t2.startTimeVal >= t.startTimeVal 
    AND t2.endTimeVal <= t.endTimeVal

CodePudding user response：

Aside from the dangers of a SQL Injection attack, as pointed out by @SMor in the comments, your question seems to indicate that the rows are coming back in the wrong order.

This can be resolved by adding an ORDER BY clause to your query:

SELECT timeTableID 
  FROM timetableTbl 
 WHERE dayVal = 'MONDAY' 
       AND StartTimeVal BETWEEN @startTime AND @endTime
       AND EndTimeVal BETWEEN @startTime AND @endTime
 ORDER BY StartTimeVal, EndTimeVal;

Note that I've converted the SQL to a parameterized query.

Now, the results will be returned such that they are in sequential order by StartTimeVal. If two rows have the same StartTimeVal, they'll be sorted by their EndTimeVal.