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
 General SQL Server Forums
 New to SQL Server Programming
 Calculating employee hours worked (nightshift)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jas
Yak Posting Veteran

South Africa
51 Posts

Posted - 05/06/2011 :  02:32:46  Show Profile  Reply with Quote
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

Australia
73 Posts

Posted - 05/06/2011 :  02:38:28  Show Profile  Reply with Quote
SELECT DATEDIFF(hh, '2011-05-06 18:00','2011-05-07 03:00')
Go to Top of Page

Jas
Yak Posting Veteran

South Africa
51 Posts

Posted - 05/06/2011 :  03:02:23  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 05/06/2011 :  03:40:26  Show Profile  Reply with Quote
what happen to this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159391 ?

The query is not working for you ?


KH
Time is always against us

Go to Top of Page

Jas
Yak Posting Veteran

South Africa
51 Posts

Posted - 05/06/2011 :  04:10:11  Show Profile  Reply with Quote
I am working with it now. I just need to get the first clocking and last clockin for the employee. Any help on that?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 05/06/2011 :  04:33:48  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Jas
Yak Posting Veteran

South Africa
51 Posts

Posted - 05/06/2011 :  04:38:41  Show Profile  Reply with Quote
Yes, but if it is nightsift, they would clock out the next day.
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