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.
| Author |
Topic |
|
WoodHouse
Posting Yak Master
211 Posts |
 Posted - 2010-06-24 : 09:37:43
|
| Hiwe have table Time_Sheet contains column Date datetime datatype and column Time has varchar dtatypeExample Time column Varchar datatypestoring format is '10:10:00'which means 10 hours, 10 minutes, 00 secondsI need to write query to each employee the total working hours minutes & seconds.help on this |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-24 : 09:39:41
|
Why didn't you use a Time datatype?  MadhivananFailing to plan is Planning to fail |
 |
|
|
WoodHouse
Posting Yak Master
211 Posts |
Posted - 2010-06-24 : 09:42:16
|
| Table has already created and one more thing we are using sql server 2000 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-06-24 : 09:45:07
|
[code]select employee_id, sum(datediff(second, 0, [Time])) as total_secondsfrom Time_Sheetwhere [Date] >= '2010-06-01'and [Date] <= '2010-06-24'group by employee_id[/code]This will give you total working hours in seconds. You should have no problem converting it to hour - minutes - seconds
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
WoodHouse
Posting Yak Master
211 Posts |
Posted - 2010-06-24 : 10:03:14
|
| Thanks KH |
|
|
|
mazirra
Starting Member
3 Posts |
Posted - 2011-06-01 : 07:56:40
|
| I have a system that enters the ID in the first column, the date and time in the second and third columns and the sense (IN/OUT) in the fourth column, for each employee that enters/exits the premises. Note that not only the in /out can occur over midnight, but also I have the situation of having two periods of the same employee in the same day.The objective is to obtain in some way a daily report for each ID (employee).unspammed |
|
|
|
|
|
|
Time Sheet calc