Click here to Skip to main content
14,638,900 members
Rate this:
Please Sign up or sign in to vote.
Looking for stored procedure to calculate total work hours of the employees with multiple in&out

Table structure
Id - primary let
EmpCode - employee code
Status - IN/OUt
ActionDatetime - DateTime
Sample record
1  emp100  IN     2017-05-18 10:00
2  emp100  OUT 2017-05-18  15:00
3   emp100  IN    2017-05-18  17:00
4  emp100  OUT  2017-05-18  20:00
5  emp102   IN   2017-05-18  06:00
6  emp102   OUT 2017-05-18 10:00
7 emp103  IN      2017-05-18 07:00
8  emp103  IN     2017-05- 18 10:00

I have to sample data like above.

I want to run a stored procedure with date.

It should give the result like.

1.List of the employees codes without repeating of employees code ( employees code can't repeat in the rows)
2. All IN and Out for all employees like.

1. Emp100 IN 2017-05-18 10:00 OUT 2017-05-18 15:00 IN 2017-05-18 17:00 OUT 2017-05-18 20:00 (total hrs )


If any in or out missed for the employees​ ex, odd numbers of in and out. Also continues same sequence of same status in in also starting of the day with out without in it should show in the last columns as a remarks.


This job was posted from a mobile device, so please pardon any typos or any missing details.

What I have tried:

USE [test]
GO
/****** Object: StoredProcedure [dbo].[GetWorkingHours] Script Date: 19-05-2017 12:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkingHours]
@Day date
AS
BEGIN
SET NOCOUNT ON;

with EmpIn as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%IN%'
),
EmpOut as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%OUT%'
)
select 
e1.EmpId as 'EmpId', 
e1.Date as 'In',
e2.Date as 'Out',
datediff(MINUTE, e1.[Date], e2.[Date]) as 'TotalHours'
from EmpIn e1
full outer join EmpOut e2 on e1.RowNum = e2.RowNum
end
Posted
Updated 20-May-17 6:45am
v3
Comments
CHill60 19-May-17 5:05am
   
Ignoring the fact that this needs to be a Stored Procedure, what queries have you written to try to get this information?
If you literally have "no idea....." on how to proceed then you are not going to understand anything we give you. We are not a code writing service by the way
Member 13210334 19-May-17 5:08am
   
USE [test]
GO
/****** Object: StoredProcedure [dbo].[GetWorkingHours] Script Date: 19-05-2017 12:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkingHours]
@Day date
AS
BEGIN
SET NOCOUNT ON;

with EmpIn as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%IN%'
),
EmpOut as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%OUT%'
)
select
e1.EmpId as 'EmpId',
e1.Date as 'In',
e2.Date as 'Out',
datediff(MINUTE, e1.[Date], e2.[Date]) as 'TotalHours'
from EmpIn e1
full outer join EmpOut e2 on e1.RowNum = e2.RowNum
end
Christian Luketa Kanyinda 20-May-17 6:30am
   
Hi,
Please try to specify in better way the table structure.
What do you means by this record : emp100 IN 2017-05-18 10:00
IN is it a columns ?
emp100 & 2017-05-18 10:00 too
Rate this:
Please Sign up or sign in to vote.

Solution 1

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

So give it a try, and see how far you can get - if you meet a specific problem, then please ask about that and we will do our best to help. But we aren't going to do it all for you!
   
Comments
CHill60 19-May-17 8:29am
   
I have to say, if it is homework, it's evil homework. I'm concerned now that I over-engineered my solution (which I did as an exercise)
Rate this:
Please Sign up or sign in to vote.

Solution 3

I had a go at this. As I said in my comment I hope this isn't homework as I found it quite difficult to get all of the information you wanted.

The first problem to deal with is the missing information. The best way to handle that is to generate a sequence of expected entries e.g.
SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
UNION ALL
SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut  WHERE CAST(ActionDatetime AS Date) = @Day
That gave me these results:
EmpCode Status
emp100	IN
emp102	IN
emp103	IN
emp100	OUT
emp102	OUT
emp103	OUT
If I then query against those results, LEFT JOINing to the table I will get at least one entry for every employee for Status = IN and at least one entry for every employee for Status = OUT. I'll also take that opportunity to create an In and Out column/i> and RANK each record by Employee by date
DECLARE @Day DATE = '2017-05-18'

if OBJECT_ID('tempdb..#shifts') is not null drop table #shifts
;with Expected as
(
	SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
	UNION ALL
	SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut  WHERE CAST(ActionDatetime AS Date) = @Day
)
select Id, AE.EmpCode, AE.[Status],ActionDatetime,
CASE WHEN AE.[Status] = 'IN' THEN ActionDatetime ELSE CAST(NULL AS datetime) END AS InTime,
CASE WHEN AE.[Status] = 'IN' THEN CAST(NULL AS datetime) ELSE ActionDatetime END AS OutTime
, RANK() OVER (PARTITION BY AE.EmpCode, AE.[Status] ORDER BY AE.EmpCode, ActionDatetime) rn
into #shifts
FROM Expected AE 
LEFT OUTER JOIN InOut actual ON AE.EmpCode = actual.EmpCode AND AE.[Status]=actual.[Status]
WHERE Id IS NOT NULL AND CAST(ActionDatetime AS Date) = @Day
gives me the results
Id EmpCode Status ActionDateTime   InTime           OutTime             rn
1  emp100  IN	  2017-05-18 10:00 2017-05-18 10:00 NULL	        1
2  emp100  OUT	  2017-05-18 15:00 NULL	            2017-05-18 15:00	1
3  emp100  IN	  2017-05-18 17:00 2017-05-18 17:00 NULL	        2
4  emp100  OUT	  2017-05-18 20:00 NULL	            2017-05-18 20:00	2
5  emp102  IN	  2017-05-18 06:00 2017-05-18 06:00 NULL	        1
6  emp102  OUT	  2017-05-18 10:00 NULL	            2017-05-18 10:00	1
7  emp103  IN	  2017-05-18 07:00 2017-05-18 07:00 NULL	        1
8  emp103  IN	  2017-05-18 10:00 2017-05-18 10:00 NULL	        2

We can tidy those results up by using GROUP BY
if OBJECT_ID('tempdb..#shifts2') is not null drop table #shifts2
SELECT EmpCode, rn, MIN(id) as Id,
MAX(InTime) as InTime, MAX(OutTime) AS OutTime
, CASE WHEN MAX(InTime) IS NULL or MAX(OutTime) IS NULL THEN 'Error' ELSE '' END AS Notes
INTO #shifts2
FROM #shifts
GROUP BY EmpCode, rn
Which gives me the much tidier results:
empCode rn      Id      TimeIn            TimeOut           Notes
emp100	1	1	2017-05-18 10:00  2017-05-18 15:00	
emp100	2	3	2017-05-18 17:00  2017-05-18 20:00	
emp102	1	5	2017-05-18 06:00  2017-05-18 10:00	
emp103	1	7	2017-05-18 07:00  NULL	            Error
emp103	2	8	2017-05-18 10:00  NULL	            Error
Note you can ignore the message "Warning: Null value is eliminated by an aggregate or other SET operation."

At this point you could choose to PIVOT the columns TimeIn and TimeOut but I chose to use the LEAD function instead
;WITH res AS
(
	SELECT rn,EmpCode, InTime AS InTime1, OutTime as OutTime1,
	LEAD(InTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS InTime2,
	LEAD(OutTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS OutTime2, 
	Notes
	FROM #shifts2
)
SELECT EmpCode,InTime1,OutTime1,InTime2,OutTime2, 
	isnull(DATEDIFF(hh, InTime1,OutTime1),0) + isnull(DATEDIFF(HH, InTime2, OutTime2),0) AS TotalHours
	, Notes
FROM res
WHERE rn = 1
Results:
emp100 2017-05-18 10:00 2017-05-18 15:00 2017-05-18 17:00 2017-05-18 20:00 8	
emp102 2017-05-18 06:00 2017-05-18 10:00 NULL	          NULL	           4	
emp103 2017-05-18 07:00 NULL	         2017-05-18 10:00 NULL	           0 Error
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100