Calculate Hours worked before default start time

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

General SQL Server Forums

New to SQL Server Programming

Calculate Hours worked before default start time

Author

Topic

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-04-13 : 09:20:29

Hi,

I am trying tp calculate how many hours a person worked before the default start time. The data that i have to work with is derived from this query:

SelectEmp.Name + ' ' + Emp.Surname As FullName, Clock.ClockTime, IO.InOrOut
FROM Employee Emp INNER JOIN ClockIns Clock ON Emp.Emp_Id = Clock.Emp_ID INNER JOIN InOrOut IO ON IO.IO_ID = Clock.IO_ID

This will give me the following:

FullName ClockTime InOrOut
PersonA 2011-04-01 06:05:00 IN
PersonA 2011-04-01 12:00:00 OUT
PersonA 2011-04-01 12:30:00 IN
PersonA 2011-04-01 19:00:00 OUT

Now I want another column to display the number of hours that a person worked before the default start time which is 8:00AM. Can anyone help with this?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-13 : 09:34:27

Duplicate... stick to the original

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159391

Corey

I Has Returned!!

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-04-13 : 09:44:21

The question that I am aksing is different to the other one.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-13 : 09:53:41

not really. See my reply in the other one. Use datediff... the early time will be

earlyTime = datediff(hh,InTime,dateadd(hh,8,dateadd(dy,datediff(dy,0,InTime),0)))

determines the DayOnly
Adds 8 hours to DayOnly

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources