|
|
Posted by Beowulf on 09/21/06 16:40
I have data coming from a telephony system that keeps track of when an
employee makes a phone call to conduct a survey and which project number
is being billed for the time the employee spends on that phone call in a
MS SQL Server 2000 database (which I don't own).
The data is being returned to me in a view (see DDL for w_HR_Call_Log
below). I link to this view in MS access through ODBC to create a
linked table. I have my own view in Access that converts the integer
numbers for start and end date to Date/Time and inserts some other
information i need.
This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).
However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?
Thanks.
--
DDL for view in MS SQL 2000 database:
CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
Initials, dbo.billing.startdate, dbo.billing.startdate +
dbo.billing.duration AS EndDate,
dbo.billing.duration, dbo.projects.name AS
PrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.description) AS ProjID6,
dbo.w_GetCallTrackProject10ID(dbo.projects.description) AS ProjID10,
dbo.billing.interactionid
FROM dbo.projects INNER JOIN
dbo.projectsphone INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.userid =
dbo.TRCUsers.UserID INNER JOIN
dbo.billing ON dbo.users.userid =
dbo.billing.userid ON dbo.projectsphone.projectid =
dbo.billing.projectid ON
dbo.projects.projectid = dbo.projectsphone.projectid
WHERE (dbo.billing.userid > 0)
ORDER BY dbo.billing.startdate
I don't have acess to the tables, but the fields in the view come
through as the following data types:
WinsID - varchar(10)
Initials - varchar(30)
startdate - long integer (seconds since 1970-01-01 00:00:00)
enddate - long integer (seconds since 1970-01-01 00:00:00)
duration - long integer (enddate - startdate)
ProjID10 - varchar(15)
interactionid - varchar(255) (the identifier for this phone call)
MS Access SQL statement for qryTelephonyData (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,
FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970
00:00:00')),"yyyy-mm-dd") AS HoursDate,
CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) AS
StartDT,
CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,
DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,
IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_Log
WHERE WinsID<>'0'
Navigation:
[Reply to this message]
|