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

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..

Code:

---

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.

Hi Dave,
It looks like an interesting problem. Is it possible for you to give me a create table script and a few insert statements? That way I don't have to take the effort to create the data.

I am not sure why the windows service would cause an issue, when the regular SQL works. That is perplexing.

Hello Adhijit,
Yes, I can do that for you, however I know no way of automating this process. So I would have to do it by hand and in which case may result in a table not identical with the original. If you know of a better way then let me know and I'll do that right away for you (running VS.net and SQL Server 2008)

If you would like I can supply sample emails of the data I'm receiving, both correct and incorrect ones.

Also I only brought up the windows service part because it seems this only occurs when the whole thing is functioning together. While the service runs and checks every 10 minutes its 95% correct, but randomly it will return these late check ins on the 20th (late 6000+ minutes). If I take a known date from an email that has invalid data and run that date through the stored procedure above I receive different but correct results (the records for the 20th are gone). The information for the events on the 20th are also all correct, it's just somehow making it through my condition when it's start and end time do not in anyway qualify as being on the 24th.

This is what we do when we share data in our organization between developers.

Give the create table script. This should include all the column names and their data types.

Give the insert statements to insert data to the tables. Since this is tedious, we have another option.
Provide an excel file for the data. The data from the excel file can be loaded via a number of utilities to the table.
As long as the data matches, we should have a good sample.

Hey, sorry for such a long delay in my response. The good news is I actually figured it out! We were in the midst of a major deployment, so I was definitely under the gun with this one.

The solution to my problem went as deep as the views. That along side bad data caused the strange results. It definitely took some time, but to dig that deep was surely a challenge. Definitely one to learn from and avoid in the future.

Thank again anyways for your generosity and advice! It'll surely be used, we're a growing company and communication between developers I'm finding out is crucial.