SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 query to calculate hours worked in time-clock application
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2003 :  08:20:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 10/29/2003 :  09:02:11  Show Profile  Visit Stoad's Homepage  Reply with Quote
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 - 10/29/2003 :  09:36:43  Show Profile  Reply with Quote
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 - 10/29/2003 :  12:43:41  Show Profile  Visit Stoad's Homepage  Reply with Quote
'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 - 02/08/2014 :  00:28:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000