Solved

What is the best way to calculate hours worked

Posted on 2016-09-14
5
70 Views
Last Modified: 2016-09-18
Currently still using SQL Server 2005.
I have a table that has one row for each clock in or clock out like the attached file (hoursworked.xlsx) Notice the first employee worked two shifts per day.

I need to report the hours worked per employee, per position and by day. I think I want to use a sort of Pivot to bring the clock in and clock out onto the same row and then calculate the difference, but I'm not sure how to do that and ensure the correct clocks are tied together.
HoursWorked.xlsx
0
Comment
Question by:dgerler
  • 2
  • 2
5 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41798105
IF you have clean input -- i.e. one CLOCKIN and one CLOCKOUT per shift -- this is rather easy and you don't actually need to join the rows at all (which is great for performance).

Is that the case?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41798147
Try this:
;with q1 as (
	SELECT 
		 [YYYYMMDD]
		,convert(datetime, cast([YYYYMMDD] as varchar(8))+' '+stuff(right('00'+cast([HHMM] as varchar(4)),4),3,0,':'), 121) as times
		,[Clock]
		,[EmpId]
		,[Name]
		,[Hire Date]
		,[Termination Date]
		,[Salaried]
		,[Hourly Rate]
		,[Position]
	FROM 
		[dbo].[HoursWorked]

) --select * from q1
,q2 as (
	SELECT 
		ins.[YYYYMMDD],
		ins.[EmpId],
		ins.[Name],
		ins.times as times_in,
		outs.times as times_out,
		ins.[Hire Date],
		ins.[Termination Date],
		ins.[Salaried],
		ins.[Hourly Rate],
		ins.[Position]
	FROM 
		q1 as ins
		inner join q1 as outs
			on outs.[EmpId]=ins.[EmpId]
			and outs.[YYYYMMDD]=ins.YYYYMMDD
			and outs.times=(select top 1 times from q1 where [EmpId]=ins.[EmpId] and [YYYYMMDD]=ins.YYYYMMDD and times>ins.times and [Clock]='CLOCKOUT' order by times)
			and outs.Clock='CLOCKOUT'
	WHERE
		ins.Clock='CLOCKIN'
) --select * from q2
,q3 as (
	SELECT 
		[YYYYMMDD],
		[EmpId],
		[Name],
		cast(sum(datediff(mi,times_in,times_out))/60.00 as decimal(7,2)) as worked_hours,
		min([Hire Date]) as [Hire Date],
		min([Termination Date]) as [Termination Date],
		min([Salaried]) as [Salaried],
		min([Hourly Rate]) as [Hourly Rate],
		[Position]
	FROM 
		q2
	group by
		[YYYYMMDD],
		[EmpId],
		[Name],
		[Position]
) select * from q3

Open in new window

0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 400 total points
ID: 41798181
This is that table I used after I imported the data from the excel file:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HoursWorked](
	[YYYYMMDD] [int] NULL,
	[HHMM] [int] NULL,
	[Clock] [nvarchar](255) NULL,
	[EmpId] [int] NULL,
	[Name] [nvarchar](255) NULL,
	[Hire Date] [int] NULL,
	[Termination Date] [nvarchar](255) NULL,
	[Salaried] [decimal](7, 2) NULL,
	[Hourly Rate] [decimal](7, 2) NULL,
	[Position] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 1051, N'CLOCKIN', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 1432, N'CLOCKOUT', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 1654, N'CLOCKIN', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 2052, N'CLOCKOUT', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160821, 1055, N'CLOCKIN', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160821, 1427, N'CLOCKOUT', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160821, 1554, N'CLOCKIN', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 2)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160821, 2016, N'CLOCKOUT', 103, N'Hugh A Zey', 20160222, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(7.50 AS Decimal(7, 2)), 2)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 822, N'CLOCKIN', 1666, N'Jason Harris', 20141116, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(9.00 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160820, 1029, N'CLOCKOUT', 1666, N'Jason Harris', 20141116, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(9.00 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160822, 729, N'CLOCKIN', 1666, N'Jason Harris', 20141116, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(9.00 AS Decimal(7, 2)), 1)
GO
INSERT [dbo].[HoursWorked] ([YYYYMMDD], [HHMM], [Clock], [EmpId], [Name], [Hire Date], [Termination Date], [Salaried], [Hourly Rate], [Position]) VALUES (20160822, 1552, N'CLOCKOUT', 1666, N'Jason Harris', 20141116, N'NULL', CAST(0.00 AS Decimal(7, 2)), CAST(9.00 AS Decimal(7, 2)), 1)
GO

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 41798347
SELECT
    EmpId, Position, YYYYMMDD,
    CAST(ROUND(SUM(seconds_elapsed) / 60.0, 0) / 60.0 AS decimal(6, 2)) AS hours_worked
FROM (
    SELECT
        EmpId, Position, YYYYMMDD,
        DATEDIFF(SECOND, '20000101', clock_datetime) * CASE WHEN Clock = 'CLOCKIN' THEN -1 ELSE 1 END AS seconds_elapsed
    FROM dbo.HoursWorked hw
    CROSS APPLY (
        SELECT CAST(CAST(YYYYMMDD AS char(8)) + ' ' + CAST(HHMM / 100 AS varchar(2)) + ':' +
            CAST(HHMM % 100 AS varchar(2)) AS datetime) AS clock_datetime
    ) AS assign_alias_names
) AS derived
GROUP BY EmpId, Position, YYYYMMDD
ORDER BY EmpId, Position, YYYYMMDD
0
 

Author Closing Comment

by:dgerler
ID: 41804133
Thank you both for your contribution.
I tried both and both worked with limitations. Due to some input  data errors, both returned inaccurate reports. However, Scott's query showed me there was a problem and Zbertoec's query, with small changes,  helped me find the erroneous data.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question