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.
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 ...
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.
Are you on SQL 2012 or later or an earlier version of SQL Server?
I am using 2012
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, '')I think that's a typo and (just in case not obvious to the O/P) should be
FROM activities a
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, '')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
;
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 33433This 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
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
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