Solved

What is the best way to calculate hours worked

Posted on 2016-09-14
5
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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 27

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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