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 INBob 10/1/2003 11:30 AM OUTBob 10/1/2003 12:30 PM INBob 10/1/2003 5:00 PM OUTI 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.0end)from tgroup by EmpName, convert(varchar(10),[DateTime],101) |
|
|
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))GOINSERT INTO myTable99 (Employee, DayWorked, TimeType)SELECT 'Bob', '10/1/2003 8:00 AM', 'IN' UNION ALLSELECT 'Bob', '10/1/2003 11:30 AM', 'OUT' UNION ALLSELECT 'Bob', '10/1/2003 12:30 PM', 'IN' UNION ALLSELECT 'Bob', '10/1/2003 5:00 PM', 'OUT'GOINSERT INTO myTable99 (Employee, DayWorked, TimeType)SELECT 'Cindy', '10/1/2003 8:00 AM', 'IN' UNION ALLSELECT 'Cindy', '10/1/2003 9:00 AM', 'OUT' UNION ALLSELECT 'Cindy', '10/1/2003 4:00 PM', 'IN' UNION ALLSELECT 'Cindy', '10/1/2003 5:00 PM', 'OUT'GOINSERT INTO myTable99 (Employee, DayWorked, TimeType)SELECT 'Stoad', '10/1/2003 11:00 PM', 'IN' UNION ALLSELECT '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 myTable99GROUP BY Employee, CONVERT(varchar(10),[DayWorked],101)GODROP TABLE myTable99GO Much better than my feeble attempts...http://www.dbforums.com/t958181.htmlBrett8-) |
|
|
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 HisTimeFROM(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.0end) HisTimefrom tgroup by EmpName, convert(varchar(10),[DateTime],101)) tt |
|
|
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 |
|
|
|
|
|