GSquared (9/17/2012)
L' Eomot Inversé (9/15/2012)
Unless there is some performance issue that I haven't spotted, it should be sensible to do this the simple and obvious way:
SELECT LoadNumber
FROM LoadTimes
WHERE (@Now >= StartTime AND (@Now < EndTime OR StartTime >= EndTime))
OR
(@Now <= EndTime AND EndTime < StartTime)
I can't see that adding or subtracting 12 hour chunks buys you anything useful, the calculation is just as complex if you do that.
Turns it all into scans instead of seeks. May not matter in this case.
But it has the advantage that it works as long as end is not more than 24 hours after start, which is quite a useful advantage - better performance but getting an incorrect result doesn't, as I said, buy you anything.
Subtracting 12 fails for for every case where start and end are both pm or both am, end is on the day after start, and end is less that 24 hours after star - eg start = 11:00, end = 10:00, or start = 22, end = 14 - it only works when start and end are both on the same day or start is pm and end is am. Won't the changes needed to make it work for all intervals under 24 hours make it do scans?
Tom