[RESOLVED] Super tricky t-sql question (where clause returning random but specific records).

[DavesChillaxin]

I have this project at the moment that compares two tables, one of check ins and the other agents assigned to schedules (a look-up table). The point is to obtain agents late for an assigned event when no check ins exist for the events scheduled time (check in time BETWEEN startTime And endTime). Also just recently I put in place conversions for time zone differences..this isn't the problem but just when I began noticing this odd behavior.

For example in -6 time zone (CST) I pass 10/24/2012 12:00 PM, this returns late agents on that date for events occurring at 12pm. But random when this process is occurring every 10 minutes do I get late check ins for 10/20/2012. If that wasn't odd enough, the start and end times of the events appearing don't event occur outside of the checking time..nothing qualifies in my where clause, yet they're still making it through. Whats even more odd is sometimes it grabs a single event from 10/20/2012, but also a mix of both 10/20/2012 and 10/24/2012.


Stored proc first hit where I pass the checking time and where the between condition is located..

ALTER PROCEDURE [dbo].[wbpr_AbsentAgent_V]
	(
	 @CheckingOccurrence As SmallDateTime = '',
	 @CheckingUTCOffset As Int = 0 -- not used at this time/self obtained.
	)
AS

	SELECT * FROM wbpv_AgentSchedule 
	WHERE (dbo.fn_CheckInCount(UserID, scheduleID) = 0 AND 
	(@CheckingOccurrence BETWEEN dbo.fn_LocalTimeDST(StartTime, StartTimeOffset, NoDST) AND dbo.fn_LocalTimeDST(EndTime, EndTimeOffset, NoDST)))
	
	RETURN

Looking at this stored procedure, none of this behavior adds up to me. Firstly passing 10/24/2012 12:00 PM into this stored procedure should only return those records with events occurring on 10/24/2012, and not 10/20/2012(specifically). Also if there was a mistake and the start bound for the between condition was somehow being set to 10/20/2012 12:00pm because of my conversions then I should see records from the 21th, 22nd, and 23rd as well.. At the moment I'm completely baffled.

If you require any more information feel free to ask and I'll provide as much as I can.


EDIT: All other records are correct(check in times and event times match up, they are late-no check ins exist), simply put the ones being returned for 10/20/2012 are the only undesired and incorrect records, while the rest are correct. This weird data is only appears once per hour(roughly) when the windows service is running and checking every 10 minutes.