Hi Jeff,
I replied to this once, but it doesnt seem to have shown up. The problem with this scenario is that you must be in a suitable position once the data is imported, to enquire as to the state of the data and any pertinent associations at a given time. You must be able to ask for data from a year ago, and know all the data and the associations to produce the correct report. In these terms, you simply need to take a snapshot of pertinent data and store it against your imported data.
I havent got time to think this through to any great length, but I can see two solutions already. The quickest and easiest is to have an attribute on the imported data that is the Supervisors ID. You would always know who the supervisor is at any given time for data imported against an agent. But you get a little redundancy with this, but its simple.
If that solution is too crude, your next best bet is to build a supervisor_agent tracking table. When an agent is assigned to a supervisor, put his start date in. When the agent is assigned to a new supervisor, update the agents "end date" for his current supervisor association, and create a new one setting the new start date for the new supervisor association. Performing a query against this table will allow you to join it with the results data, to extrapolate the information you require.
Hope that gives you some ideas.
nick robinson
site :
www.fromconcept.co.uk
blog :
www.fromconcept.co.uk/weblog.aspx
----- Jeff Shantz wrote: -----
Hello all,
This may not be the appropriate forum, but I'm going to try my luck.
I'm designing an ASP.NET application in C# for my company. The
application grabs call center statistics for CSR's on a nightly basis,
and imports that data into its own database. Thus, the application is
used mainly for trending/reporting.
The agents are assigned to supervisors. One design aspect that I'm
struggling with is that agents can be reassigned to different
supervisors at any time. But if agent X gets reassigned to Supervisor
B, we still want Agent X's old statistics to appear in team reports
for Supervisor A, when those reports are generated during the time
period where Agent X was still under Sup A.
I've been running through various scenarios in my head, but I can't
seem to find an attractive method. Is this possible to doing on the
query-level using SQL, or am I going to have to grab the data into a
dataset and manipulate it at the application level, checking each
record to see if the agent belonged to that supervisor at that
particular period in time?
I hope I've explained this properly. Any suggestions would be
appreciated.
