Switches / Flip Flops / Iterations

[joe789]

Hi Folks,

I am wondering what is the best way to tackle this request and am really not sure. I am trying to determine the number of what I call flip flop interations. An individual can be in group a or group b at any given time, but for each day of service on record must be in at least one of the two groups. The individual can switch between groups as well. I am attempting to determine that number of times an individual switches in a 3 year period. For example:

Person #1 Sorted by Date of Service:

Feb 20, 2006 - Group A
Feb 22, 2006 - Group A
March 10, 2006 - Group A
Jan 4, 2007 - Group B
April 1, 2007 - Group A

So the above individual switched twice, starting at Group A.

So the result for this individual may be something like:


ID Start Group Flip Flops

Person#1 Group A 2 Times

If someone can give me any ideas how to tackle this, I would greatly appreciate it. The database is pretty large with about 35,000 unique clients and some of them never flip flop but other can up to 13 times per year, and this is for 3 year time period. Even if I can just get the # of times an individual flip flopped will help tremendously, but the start group would be nice as well. I know there is a way to do this with a query, but I am just not sure where to start ...

Thank you very much,

Joe

 

[pbaldy]

I can't think of any pure SQL solution that would get you what you want. I think you'll need a function that you pass a person to that returns the number of flip flops for that person. It would open a recordset on that person with the records in date order. Set a variable to the group to start with, then step through the records, comparing each record to the one before. Then you could run a query calling that function and get a listing of how many by person. I suspect it will be slow though.

 

[gemma-the-husky]

i agree with paul

because each record is dependent on other records, you will only get this by iterating a recordset.

but i dont think this will be slow

add a field to the employeename, called flipflops or something

create aquery sorted by agent/date then process the query, and store the flipflop counts. It will only take a few seconds to do 35000 items

for the future, why not store the flipflop event, so its easy to count the number of flipflops