Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 query to calculate hours worked in time-clock application

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-29 : 08:20:30
Cindy writes "I have created a time-clock applications where employees can clock in and out throughout the day. The data is stored in a SQL Server table in the following format:

Employee DateTime TimeType
-------- ------------------ ---------
Bob 10/1/2003 8:00 AM IN
Bob 10/1/2003 11:30 AM OUT
Bob 10/1/2003 12:30 PM IN
Bob 10/1/2003 5:00 PM OUT

I want to write a view that will display the number of hours an employee worked on any given day. Eg. in the example above, Bob worked 3.5 hours in the morning and 4.5 hours in the afternoon, for a total of 8 hours on 10/1/2003. I am not sure how to setup a select statement to do this though."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-29 : 09:02:11
select EmpName, convert(varchar(10),[DateTime],101),
sum(
case when TimeType='IN' then
-1*(datepart(hh,[DateTime])+datepart(mi,[DateTime])/60.0)
else datepart(hh,[DateTime])+datepart(mi,[DateTime])/60.0
end)

from t
group by EmpName, convert(varchar(10),[DateTime],101)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-29 : 09:36:43
Damn, that's good...just hav a slight problem with overnighters...


CREATE TABLE myTable99 (Employee varchar(10), DayWorked DateTime, TimeType char(3))
GO

INSERT INTO myTable99 (Employee, DayWorked, TimeType)
SELECT 'Bob', '10/1/2003 8:00 AM', 'IN' UNION ALL
SELECT 'Bob', '10/1/2003 11:30 AM', 'OUT' UNION ALL
SELECT 'Bob', '10/1/2003 12:30 PM', 'IN' UNION ALL
SELECT 'Bob', '10/1/2003 5:00 PM', 'OUT'
GO
INSERT INTO myTable99 (Employee, DayWorked, TimeType)
SELECT 'Cindy', '10/1/2003 8:00 AM', 'IN' UNION ALL
SELECT 'Cindy', '10/1/2003 9:00 AM', 'OUT' UNION ALL
SELECT 'Cindy', '10/1/2003 4:00 PM', 'IN' UNION ALL
SELECT 'Cindy', '10/1/2003 5:00 PM', 'OUT'
GO
INSERT INTO myTable99 (Employee, DayWorked, TimeType)
SELECT 'Stoad', '10/1/2003 11:00 PM', 'IN' UNION ALL
SELECT 'Stoad', '10/2/2003 8:00 AM', 'OUT'
GO

SELECT Employee
, convert(varchar(10),DayWorked,101)
, SUM(CASE WHEN TimeType='IN' THEN -1*(datepart(hh,[DayWorked])+datepart(mi,[DayWorked])/60.0)
ELSE datepart(hh,[DayWorked])+datepart(mi,[DayWorked])/60.0
END)
FROM myTable99
GROUP BY Employee, CONVERT(varchar(10),[DayWorked],101)
GO

DROP TABLE myTable99
GO


Much better than my feeble attempts...

http://www.dbforums.com/t958181.html



Brett

8-)
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-29 : 12:43:41
'Overnighters' is the easiest part of the subject:

select EmpName, HisDate,

case when HisTime<0 then 24+HisTime else HisTime end HisTime

FROM
(
select EmpName, convert(varchar(10),[DateTime],101) HisDate,
sum(
case when TimeType='IN' then
-1*(datepart(hh,[DateTime])+datepart(mi,[DateTime])/60.0)
else datepart(hh,[DateTime])+datepart(mi,[DateTime])/60.0
end) HisTime
from t
group by EmpName, convert(varchar(10),[DateTime],101)
) tt
Go to Top of Page

terredean111
Starting Member

3 Posts

Posted - 2014-02-08 : 00:28:19
Oh that’s great. You all are building one for yourself. I will be very happy to help you. I too deal in Time attendance systems business. You can try the 30 days trial pack also. And that will give you an idea. Please visit http://www.labortimetracker.com/features.cfm
Go to Top of Page
   

- Advertisement -