SQLTeam.com | Weblogs | Forums

Time card Hours


#1

Can some one help me in solving this.
I have a situation where I have to calculate Employee time card work hours,break time , total hours,start time end time. I was able to calculate the differences between enter and exit and get the hours worked, but there are some cases where there are two exits and two enters. I have to consider the first Enter if two enter are coming consequently the same case with Exits.Please let me know how can we do this. Code is unique for each employee.


#2

I have something close , it need a little touch(I don't have time in this mom)

but here it is:

;WITH cte AS
(SELECT 6665157 AS Code,'ENTER' AS Activity,'2016-05-17 08:13:43' AS TimeDate UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 08:14:17'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:14:49'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:24:23'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 12:26:04'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 12:26:39'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:33:22'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 13:02:43'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 13:48:48'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 18:11:32'			  
 )
,cteFilter
AS(
SELECT
	Code
	,Activity
	,MAX(TimeDate)  AS Max_TimeDate
FROM
(
	SELECT 
		Code
		,Activity
		,TimeDate		
		,ROW_NUMBER() OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,TimeDate),0) ORDER BY TimeDate ASC) 
		- ROW_NUMBER() OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,TimeDate),0),Activity ORDER BY TimeDate ASC) AS Grp		
	FROM
		cte AS A
)A
GROUP BY
	Code
	,Activity
	,GRP
)
,cteWithNextRow
AS(
SELECT 
	Code
	,Activity
	,Max_TimeDate 
	,LEAD(Max_TimeDate,1,Max_TimeDate) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC) AS Max_TimeDate_NextRow
FROM 
	cteFilter	
--ORDER BY 
	--Max_TimeDate
)
,cteFinal
AS(
SELECT
	Code
	,Activity
	,Max_TimeDate
	,Max_TimeDate_NextRow
	,DATEDIFF(SECOND,Max_TimeDate,Max_TimeDate_NextRow) AS DiffSec
FROM
	cteWithNextRow
)

SELECT	DISTINCT
	Code
	,MIN(MAX_TimeDate) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC
					ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS StartTime
	,MAX(MAX_TimeDate) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC
					ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EndTime
	,SUM(CASE WHEN Activity = 'ENTER' THEN DiffSec ELSE 0 END) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC
					ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS WorkHoursInSec
	,SUM(DiffSec) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC
					ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS TotalHours
	,SUM(CASE WHEN Activity = 'EXITS' THEN DiffSec ELSE 0 END) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,Max_TimeDate),0) ORDER BY Max_TimeDate ASC
					ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS BreakTime
FROM
	cteFinal

The output for this is:

Code        StartTime           EndTime             WorkHoursInSec TotalHours  BreakTime
6665157     2016-05-17 08:14:17 2016-05-17 18:11:32 31173          35835       4662

I know is not the same as your's output ...


#3

Thank you Stepson for the reply. I wanted to get workhours as 33433. Currently I am getting 30599 which is not correct. Could you please let me know if you could get it.


#5

Are you on SQL 2012 or later or an earlier version of SQL Server?


#6

I am using 2012


#7

Awesome! Then you can get the rows you need to process like below (if you weren't on at least SQL 2012, "LAG" would not be available to use).

That's just the raw rows not the totals yet., since I'm not 100% sure yet what you need. I assume you need to GROUP BY code, no problem there, but do you need to calc by work shift or do you just need an overall total for each distinct code? If by work shift, can that be across more than one calendar day, such as starting work at 6pm and ending at 3am?

;WITH activities AS (
    SELECT
        *,
        LAG(activity) OVER(PARTITION BY code ORDER BY timedate) AS previous_activity
    FROM #activities
)
SELECT a.*
FROM cte a
WHERE a.activity <> ISNULL(a.previous_activity, '')

#8

I think that's a typo and (just in case not obvious to the O/P) should be

FROM activities a

#9

Quite right. I messed up the table names because I created a temp table for testing rather than use a CTE.

Then I tried to go back and make my code compatible with the cte that had been posted earlier to provide data. Here's the corrected version.

WITH cte AS ( 
    ...<raw_data_cte_as_shown_above_that_provides_raw_table_rows>... 
),
activities AS (
    SELECT
        *,
        LAG(activity) OVER(PARTITION BY code ORDER BY timedate) AS previous_activity
    FROM cte
)
SELECT a.*
FROM activities a
WHERE a.activity <> ISNULL(a.previous_activity, '')

#10

People with 2008 engine could do:

with cte1
  as (select code
            ,activity
            ,timedate		
            ,datediff(day,0,timedate) as d
            ,row_number() over(partition by code
                                           ,datediff(day,0,timedate)
                               order by timedate
                              )
             as rn
        from yourtable
       where activity in ('ENTER','EXIT')
     )
    ,cte2
  as (select a.code
            ,a.activity
            ,a.timedate
            ,a.d
            ,row_number() over(partition by a.code
                                           ,a.d
                               order by a.timedate
                              )
             as rn
        from cte1 as a
             left outer join cte1 as b
                          on b.code=a.code
                         and b.activity=a.activity
                         and b.rn=a.rn-1
       where b.code is null
     )
select a.code
      ,cast(dateadd(day,0,a.d) as date) as dt
      ,sum(datediff(second,a.timedate,b.timedate)) as sec
  from cte2 as a
       inner join cte2 as b
               on b.code=a.code
              and b.activity='EXIT'
              and b.rn=a.rn+1
 where a.activity='ENTER'
 group by a.code
         ,a.d
;

#11

Try this:

;WITH cte AS
(SELECT 6665157 AS Code,'ENTER' AS Activity,'2016-05-17 08:13:43' AS TimeDate UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 08:14:17'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:14:49'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:24:23'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 12:26:04'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 12:26:39'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 12:33:22'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 13:02:43'			  UNION ALL
 SELECT 6665157        ,'ENTER'            ,'2016-05-17 13:48:48'			  UNION ALL
 SELECT 6665157        ,'EXITS'            ,'2016-05-17 18:11:32'			  
 )

SELECT 
	X1.Code
	,X1.Activity AS CurrentActivity	
	,DATEDIFF(SECOND
			,X1.TimeDate
			,LEAD(X1.TimeDate,1,X1.TimeDate) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,X1.TimeDate),0) ORDER BY X1.TimeDate ASC)) AS [diffBetweenRowXandRowX+1]
FROM	
	cte AS X1

Here are the difference between current row and next row:

Code        CurrentActivity diffBetweenRowXandRowX+1
6665157     ENTER           34
6665157     ENTER           14432
6665157     EXITS           574
6665157     EXITS           101
6665157     ENTER           35
6665157     ENTER           403
6665157     EXITS           1761
6665157     ENTER           2765
6665157     ENTER           15764
6665157     EXITS           0

Summing all this

SELECT
	A.Code
	,SUM(A.[diffBetweenRowXandRowX+1]) AS WorkHours
FROM
(
SELECT 
	X1.Code
	,X1.Activity AS CurrentActivity	
	,DATEDIFF(SECOND
			,X1.TimeDate
			,LEAD(X1.TimeDate,1,X1.TimeDate) OVER(PARTITION BY Code , DATEADD(DAY,DATEDIFF(DAY,0,X1.TimeDate),0) ORDER BY X1.TimeDate ASC)) AS [diffBetweenRowXandRowX+1]
FROM	
	cte AS X1
)A
WHERE
	A.CurrentActivity ='ENTER'
GROUP BY
	A.Code

I get this value:

Code        WorkHours
6665157     33433

#12

This value looks correct. BUt have to check with multiple codes as well. The Enter and EXITS are alias values created
by me.

select distinct ioname, CASE WHEN IOName IN('Facility Door','Break Room','North Main Passback Reader','South Main Passback Reader')
THEN 'EXITS'
ELSE 'ENTER'
END AS Activity
from [dbo].[EvnLog]
where timedate>='05/17/2016' and timedate<'05/18/2016'

ioname Activity
2nd Floor North Door 1 ENTER
2nd Floor North Door 3 ENTER
Excutive Hallway 2 ENTER
2nd Floor South Door 2 ENTER
Facility Door EXITS
North Main Passback Reader EXITS
Tech Room ENTER
Executive Hallway 3 ENTER
2nd Floor South Door 1 ENTER
2nd Floor South Door 3 ENTER
Server Room ENTER
North Main Entry Doors ENTER
South Main Passback Reader EXITS
South Main Entry Doors ENTER
2nd Floor North Door 2 ENTER
IT Hallway ENTER
Break Room EXITS


#13

HI All let me make it more detailed. There are two exits continuously because a person can go to Facility room which is exit and then exit the building which is possible. There are some possibilities where the person can enter more than once in the building as there are card readers for each entry.The Enter and EXITS are alias values created
by me. Thank you all for you help.
below are the different access cards
select code,timedate,ioname, CASE WHEN IOName IN('Facility Door','Break Room','North Main Passback Reader','South Main Passback Reader')
THEN 'EXITS'
ELSE 'ENTER'
END AS Activity
from [dbo].[EvnLog]
where timedate>='05/17/2016' and timedate<'05/18/2016'

ioname Activity
2nd Floor North Door 1 ENTER
2nd Floor North Door 3 ENTER
Excutive Hallway 2 ENTER
2nd Floor South Door 2 ENTER
Facility Door EXITS
North Main Passback Reader EXITS
Tech Room ENTER
Executive Hallway 3 ENTER
2nd Floor South Door 1 ENTER
2nd Floor South Door 3 ENTER
Server Room ENTER
North Main Entry Doors ENTER
South Main Passback Reader EXITS
South Main Entry Doors ENTER
2nd Floor North Door 2 ENTER
IT Hallway ENTER
Break Room EXITS

select code,timedate,ioname, CASE WHEN IOName IN('Facility Door','Break Room','North Main Passback Reader','South Main Passback Reader')
THEN 'EXITS'
ELSE 'ENTER'
END AS Activity
from [dbo].[EvnLog]
where timedate>='05/17/2016' and timedate<'05/18/2016'

and code='6665157'
and ioname<>''

code timedate ioname Activity
6665157 2016-05-17 08:13:43.000 North Main Entry Doors ENTER
6665157 2016-05-17 08:14:17.000 IT Hallway ENTER
6665157 2016-05-17 12:14:49.000 Facility Door EXITS
6665157 2016-05-17 12:24:23.000 North Main Passback Reader EXITS
6665157 2016-05-17 12:26:04.000 North Main Entry Doors ENTER
6665157 2016-05-17 12:26:39.000 IT Hallway ENTER
6665157 2016-05-17 12:33:22.000 Facility Door EXITS
6665157 2016-05-17 13:02:43.000 IT Hallway ENTER
6665157 2016-05-17 13:48:48.000 Tech Room ENTER
6665157 2016-05-17 18:11:32.000 North Main Passback Reader EXITS


#14

create table #act
(
 Code varchar(10),
 Activity varchar(10),
 timeDate datetime
)

insert into #act 
values

('6665157', 'ENTER',  '20160517 8:13:43.000'     ),
('6665157', 'ENTER',  '20160517 8:14:17.000' ),
('6665157', 'EXITS',  '20160517 12:14:49.000' ),
('6665157', 'EXITS',  '20160517 12:24:23.000' ),
('6665157', 'ENTER',  '20160517 12:26:04.000' ),
('6665157', 'ENTER',  '20160517 12:26:39.000' ),
('6665157', 'EXITS',  '20160517 12:33:22.000' ),
('6665157', 'ENTER',  '20160517 13:02:43.000' ),
('6665157', 'ENTER',  '20160517 13:48:48.000' ),
('6665157', 'EXITS',  '20160517 18:11:32.000' )


with ctefilter
as
(
  select ROW_NUMBER() over(partition by Activity, datePart(hour,timeDate) order by timeDate asc) as c, e.* from #act as e
),
filter2
as
(
select Code,Activity, timeDate, 
'StartTime'=   (Case when Activity='ENTER' THEN timeDate end),
'EndTime'=(Case when Activity='EXITS' THEN timeDate end)  
from ctefilter
where c <> 2
)
,
 filter3
as
(
select Code,Activity, timeDate, 
'StartTime'=  Coalesce(  (Case when Activity='ENTER' THEN timeDate end), endtime),
'EndTime'= Coalesce( (Case when Activity='EXITS' THEN timeDate end),starttime) 
 from filter2
 )
 ,
 filter4
 as
 (
 select Code, Activity,timedate, StartTime, Lead(endtime) over(partition by Code order by timeDate) as endtime 
   
  from filter3 

  )
  ,
  filter5
  as
  (
  select f.*,   Datediff(SECOND,  timedate,  endtime ) as Diff_In_Out  from filter4 as f
  where StartTime is not null and endtime is not null 
 )

 select l.*, sum(Diff_In_Out) over(partition by Activity order by Code) as Total_in_Second from filter5 as l
  order by timedate