Solved

# What is the best way to calculate hours worked

Posted on 2016-09-14
78 Views
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
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

LVL 69

Expert Comment

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

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
``````
0

LVL 27

Accepted Solution

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
``````
0

LVL 69

Assisted Solution

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

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

Question has a verified solution.

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

### Suggested Solutions

Title # Comments Views Activity
Retrieving JSON data as OUT parameter in Sql Server CLR procedure 1 43
SQL Server 2012 to SQL Server 2016 24 57
SQL Convert rows to columns 5 34
SQL syntax for max(date) 3 39
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
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
###### Suggested Courses
Course of the Month3 days, 9 hours left to enroll

#### 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.