Expert: Scottgem - 2/20/2005

Question
Hi,

I am facing some problem related to scheduling the people in shift. We have 3 shifts coming in our office. They all login to avaya and i get login logut report for them in the below format:
Agent Name   Login Time   Logout Time
DB Nasser M   05/02/2005 16:04   05/02/2005 17:10
DB Nasser M   05/02/2005 17:11   05/02/2005 17:13
DB Nasser M   05/02/2005 17:22   05/02/2005 17:22
DB Nasser M   05/02/2005 17:23   05/02/2005 21:28
DB Nasser M   05/02/2005 21:28   05/02/2005 23:00
DB Maryam M   05/02/2005 8:24   05/02/2005 9:54
DB Maryam M   05/02/2005 10:04   05/02/2005 13:49
DB Maryam M   05/02/2005 14:03   05/02/2005 15:08
DB Ahmed Rafaat   05/02/2005 22:57   06/02/2005 1:45
===========================================
All of them are assigned in some or other shifts and with above data we need find out(there can be multiple entries in single day of login and logout. People are even doing night shift and there time comes in next day):

1) What shift they were assigned to and if they logged in late or logged off early from there shift.
2) We need to figure out on 15 min interval basis for whole day how many people were logged in. So there would be one table of requirement of 15 min interval for each day and then match with above login logout how many were logged in. In case people were less then required then auto mail or alarm.

Example: In case a agent like nasser is coming in shift 7:30 in morning so we need to see if he logged in this time or not he was late by how many minutes or was he absent he was supposed to logout did he logout early.

Second with all these people in shift what was the actual no of agents in a shift in 15 min interval. like 7:30 4 agents 7:45 3 agents.

I am not a programmer and trying to fgiure out the best way to design tables in such way i can calculate time and tweak the raw data to get the results. Please help me out

Thanks and have a nice day

Regards
Dhruv

Answer
I assume you can get the data into a table with the structure: LoginID, Timein, TimeOut. You then need another table, that shows the shifts they were scheduled for on any day. From there its a matter of constructing queries to return the data you you want.

Using your examples: to find out if Nasser clocked in early or late on 5/2 could use an expression like this:

SchedTime-DMIN("[Timein]","TimeLog","[LoginID] = 'Nasser M' AND [Timein] = #5/2/05#")

Then check to see if the result of the expression is positive or negative to determine if he was late or not.

To find out how many people were clocked in for a period you would run a query using a Criteria of TimeIn <= start time and Time Out >= End time.

HTH
Scott<>
P.S. the expressions are off the top of my head so you may need to double check syntax.