About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
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 Hi Druv
This is an involved piece of work, and I suggest that if you are not comfortable programming in VBA, working with SQL and generating reports/outputs, then you will be struggling completely.
I can do this work in its entirety for you, but only on a commercial basis - we will need to agree a full specification and contract amount.
In the spirit of AllExperts.com, however, here are some guidelines:
A) Shift adherence
1. Part from your logging table, you will need a table of shift patterns and a table of time periods, then perhaps a reporting table that is build up by a procedure, that integrates this information to build reports.
2. The primary key of the reporting table would be something like:
Date / Shift / Name
3. The attribute data could include:
Earliest logon time
Lastest logoff time
Logon advance (Minutes early or late)
Logoff advance (minutes early or late)
Total minutes logged on.... etc.
You could then build our reports based on this table.
B) Shift Cover
You can use a query to count the number of logged on agents against the Time period table.
Lets say in this table (tblPeriod) you have
PeriodID
P_Start
P_End
which defines the start date/time and end date/time of each period. PeriodID is the primary key - you may want to add a descriptive column to improve readability.
Given this, the following query will show the number agents by time period, given some assumptions about the logging table (see the SQL below):
SELECT T2.PeriodID, Count(T1.AgentID) As NumAgents
FROM tblLogging AS T1, tblPeriod AS T2
WHERE T1.LogOnTime >= T2.P_Start AND T1.LogOffTime <= T2.P_End
GROUP BY T2.PeriodID;
