Find employee work hours in time range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sqlTest - 17 Jul 2008 11:50 GMT

Hello,
I have the table "EmployeesTimeWork" with the structure:

IdEmployee Date TimeIn TimeOut
------------------------- --------- ------------------

In other I have the 1 day ranges from the hours like
5-6,6-7,...12-13,..23:0,..to 4-5

I have sumarizing the total hours works by range but I'm not find the
solution.
The most complex situation-case is when the employe work start at
23hour (example) and
to finally at 3:00 (example)

Also I work with sql server 2005

A lot of thanks in advance and sorry for my english

Reply to this Message

Uri Dimant - 17 Jul 2008 12:22 GMT

sqlTest
A few days ago a SQL Server MVP Steve Kass has posted simple script to get a
difference between two days

In your case , construct Date+TimeIN and Date +TimeOUT and compare by using
Steve's script

WITH Secs(s) AS (

SELECT DATEDIFF(second, '2008-06-07T21:30:05',
2008-06-08T02:33:15') ----Add here from your table.....

)

SELECT RIGHT(s/3600,6) + ':'

+ RIGHT(100+s%3600/60,2) + ':'

+ RIGHT(100+s%60,2)

FROM Secs;

> Hello,
> I have the table "EmployeesTimeWork" with the structure:
[quoted text clipped - 14 lines]
>
> A lot of thanks in advance and sorry for my english

Reply to this Message