|
|
Posted by Beowulf on 09/22/06 15:40
Erland Sommarskog wrote:
> 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.
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_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.
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.
Navigation:
[Reply to this message]
|