Calculating employee hours worked (nightshift)

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

Calculating employee hours worked (nightshift)

Author

Topic

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-06 : 02:32:46

Hi,

I am trying to do a calculation for the time worked by employees that work during the nightshift. Eg. from 18:00 to 3:00AM the next day.

Any clues on how to go about doing this?

dshelton
Yak Posting Veteran

73 Posts

Posted - 2011-05-06 : 02:38:28

SELECT DATEDIFF(hh, '2011-05-06 18:00','2011-05-07 03:00')

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-06 : 03:02:23

I have the following code:

; with cte as (select Emp.Emp_ID, Rules.Rule_ID, SDay.SDay_ID, Emp.Name + ' ' + Emp.Surname As FullName, Clock.ClockTime, Dir.Name, SDay.NormalDay,
Convert(varchar,Rules.Default_Shift_End,8) AS DefaultShiftEnd,
Convert(varchar,Rules.Default_Shift_Start,8) AS DefaultShiftStart,
row_no = row_number() over (partition by Emp.Name + ' ' + Emp.Surname order by Clock.ClockTime) from Employee Emp INNER JOIN ClockIns Clock ON Emp.Emp_Id = Clock.Emp_ID
INNER JOIN Direction Dir ON Clock.Direction_ID = Dir.Direction_ID
INNER JOIN EmpShift ON Emp.Emp_ID = EmpShift.Emp_ID
INNER JOIN Rules ON Rules.Rules_ID = EmpShift.Rules_ID
INNER JOIN SDay ON Rules.Rules_ID = SDay.Rules_ID ),
Cte2 as ( select c1.FullName, ClockIn = c1.ClockTime, ClockOut = case when c2.Name = 'OUT' then c1.ClockTime else dateadd(day, datediff(day, 0, c1.ClockTime), 0) + DateAdd(dd, DateDiff(dd, 0, c1.DefaultShiftEnd), 0) end, DefaultStart = c1.DefaultShiftStart, DefaultEnd = c1.DefaultShiftEnd, c1.NormalDay
from cte c1
left join cte c2 on c1.row_no = c2.row_no - 1 and c1.FullName = c2.FullName where c1.Name = 'IN')
select NormalDay = case when NormalDay = 1 then 'True'
when NormalDay = 0 then 'False' end, FullName, LEFT(FullName, 3)+convert(nvarchar(10),ClockIn, 12) As DailyHrsName,
Date = convert(nvarchar(10),ClockIn, 111), MIN(ClockIn) As Calc_first_clock, MAX(ClockOut) As Calc_last_clock, Calc_work_start_time = case when StartRounding_Up = 1 then convert(smalldatetime,ROUND(cast(MIN(ClockIn) as float) * (24/StartRounding_Hrs_Num),0)/ (24/StartRounding_Hrs_Num))
when StartRounding_Up = 0 then DATEADD(Hour, DATEDIFF(Hour, 0, MIN(ClockIn)), 0) end, Calc_work_end_time = case when EndRounding_Up = 1 then convert(smalldatetime,ROUND(cast(MAX(ClockOut) as float) * (24/EndRounding_Num_Hrs),0)/ (24/EndRounding_Num_Hrs)) when EndRounding_Up = 0 then DATEADD(Hour, DATEDIFF(Hour, 0, MAX(ClockOut)), 0) end,
TotalHours = CAST(datediff(minute, Min(ClockIn), MAX(ClockOut)) / 60.0 as decimal(4,2))
FROM cte2
GROUP BY convert(nvarchar(10), ClockIn, 111),FullName, NormalDay, convert(nvarchar(10),ClockIn, 12), DefaultStart, DefaultEnd

This works fine for employees that clocked in and out ont he same day. When they clocked in on one day, but clocked out on a different day, I get "Calc_last_Clock as "00:00:000"

Any help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-06 : 03:40:26

what happen to this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159391 ?

The query is not working for you ?

KH
[spoiler]Time is always against us[/spoiler]

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-06 : 04:10:11

I am working with it now. I just need to get the first clocking and last clockin for the employee. Any help on that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-06 : 04:33:48

quote:
Originally posted by Jas

I am working with it now. I just need to get the first clocking and last clockin for the employee. Any help on that?

first & last clockin for the employee by day ?

KH
[spoiler]Time is always against us[/spoiler]

Jas
Yak Posting Veteran

51 Posts

Posted - 2011-05-06 : 04:38:41

Yes, but if it is nightsift, they would clock out the next day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources