Solved

What is the best way to calculate hours worked

Posted on 2016-09-14
5
51 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:ScottPletcher
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:ScottPletcher
ScottPletcher 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now