Breaking down Total Hours worked into Day and Evening hours

[Beowulf]

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 qryTelephonyDat a 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_L og
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.Wi nsID, dbo.users.usern ame AS
Initials, dbo.billing.sta rtdate, dbo.billing.sta rtdate +
dbo.billing.dur ation AS EndDate,
dbo.billing.dur ation, dbo.projects.na me AS
PrjName, dbo.w_GetCallTr ackProject6ID(d bo.projects.des cription) AS ProjID6,

dbo.w_GetCallTr ackProject10ID( dbo.projects.de scription) AS ProjID10,
dbo.billing.int eractionid
FROM dbo.projects INNER JOIN
dbo.projectspho ne INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.useri d =
dbo.TRCUsers.Us erID INNER JOIN
dbo.billing ON dbo.users.useri d =
dbo.billing.use rid ON dbo.projectspho ne.projectid =
dbo.billing.pro jectid ON
dbo.projects.pr ojectid = dbo.projectspho ne.projectid
WHERE (dbo.billing.us erid 0)
ORDER BY dbo.billing.sta rtdate

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 qryTelephonyDat a (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_L og.WinsID, dbo_w_HR_Call_L og.ProjID10,
FORMAT(CDATE(DA TEADD('s',start date-(5*60*60),'01-01-1970
00:00:00')),"yy yy-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 StartDTDayOfWee k, Duration,
IIf(StartDTDayO fWeek=1 Or StartDTDayOfWee k=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_L og
WHERE WinsID<>'0'

[Erland Sommarskog]

Re: Breaking down Total Hours worked into Day and Evening hours

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

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 qryTelephonyDat a 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?

It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

CREATE VIEW dbo.w_HR_Call_L og
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.Wi nsID, dbo.users.usern ame AS
>...
ORDER BY dbo.billing.sta rtdate

I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.

If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Beowulf]

Re: Breaking down Total Hours worked into Day and Evening hours

Erland Sommarskog wrote:

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

>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 qryTelephonyDat a 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?

>
It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first? Do you have any pointers
to good tutorials on calendar tables (or is google my friend)? It's a
concept I haven't heard of before.

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

As returned by the view, the startdate and enddate are integers (number
of seconds since 1970-01-01 00:00:00) so it's fairly simple to convert
to UTC.

>CREATE VIEW dbo.w_HR_Call_L og
>AS
>SELECT TOP 100 PERCENT dbo.TRCUsers.Wi nsID, dbo.users.usern ame AS
>...
>ORDER BY dbo.billing.sta rtdate

>
I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.
>
If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

Thank you for the advice. I learned that fact a little while ago in
this very newsgroup. I don't own that particular view, though.

[Erland Sommarskog]

Re: Breaking down Total Hours worked into Day and Evening hours

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first?

I don't know. That is, I don't know what your business requirements are,
so I cannot answer. I made the simple assumption that only the start time
applied. If you want to split a call that started at 17:23 and ended at
18:14 into day and evening, I don't know in which way you want to split it.

<Standard rant>

Please post:

o CREATE TABLE(s) statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

That makes it possible to easily copy and paste to develop a tested
solution.

</Standard rant>

(It's not likely that it will be me this time though, as I'm
off for vacation tomorrow.)

Do you have any pointers to good tutorials on calendar tables (or is
google my friend)? It's a concept I haven't heard of before.

http://www.aspfaq.com, search for calendar. Aaron has several entries
on them.

Essentially a calendar is a table with one row for each day, and then
you associate attributes to the days that are appropriate for your
business like IsWorkingDay.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx