|
|
Posted by Erland Sommarskog on 09/21/06 22:11
Beowulf (beowulf_is_not_here@hotmail.com) 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 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?
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_Log
> AS
> SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
> ORDER BY dbo.billing.startdate
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@sommarskog.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
Navigation:
[Reply to this message]
|