[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

What is the best way to calculate hours worked

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
dgerler
Asked:
dgerler
  • 2
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
dgerlerAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now