May 19, 2016 at 7:14 am
HI
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.
iocode oprActivitytimedate
56665157468ENTER2016-05-17 08:13:43.000
56665157468ENTER2016-05-17 08:14:17.000
56665157468EXITS2016-05-17 12:14:49.000
56665157468EXITS2016-05-17 12:24:23.000
56665157468ENTER2016-05-17 12:26:04.000
56665157468ENTER2016-05-17 12:26:39.000
56665157468EXITS2016-05-17 12:33:22.000
56665157468ENTER2016-05-17 13:02:43.000
56665157468ENTER2016-05-17 13:48:48.000
56665157468EXITS2016-05-17 18:11:32.000
May 19, 2016 at 7:38 am
Welcome to the forums.
Check this link out on how to post these kinds of questions here. http://www.sqlservercentral.com/articles/Best+Practices/61537/
Since this is your first post I'll give you this first one as a freebie.
DECLARE @TimeCard TABLE ([IO] SMALLINT, Code INT, OPR INT, Activity VARCHAR(15), ActDate DATETIME)
INSERT INTO @TimeCard
VALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 08:14:17.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:24:23.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:04.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:39.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:33:22.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:02:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:48:48.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 18:11:32.000')
SELECT * FROM @TimeCard
Can you post an example of how you want it to look like.
Cheers!
May 19, 2016 at 8:09 am
code starttime endtime workhoursinsecTotalHours Breaktime
66651572016-05-17 08:14:17.0002016-05-17 18:11:32.00030599 35835 5236
Actual Work hours should be
Workhours
33433
I am using the following query to get first result.
with cte as(
select *,--LAG(Activity, 1, 'N/A') OVER ( PARTITION BY code ORDER BY timedate ) AS LastEvent ,
LEAD(Activity, 1, 'N/A') OVER ( PARTITION BY code ORDER BY timedate ) AS NextEvent,LEAD(timedate,1,NULL) OVER ( PARTITION BY code ORDER BY timedate ) as outt FROM (SELECT *,
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 io=5
and code='6665157'
)t)
SELECT code,starttime,endtime,workhours as workhoursinsec ,datediff(second,starttime,endtime)AS TotalHours,datediff(second,starttime,endtime)-Workhours Breaktime FROM(
SElect enter.Code,SUM(worktime) AS Workhours,MIn(timedate) AS Starttime,ENDtime FROM
(select ioname,code,activity,nextevent,timedate,outt
,datediff(second,timedate,outt) worktime from cte where activity<>nextevent
and activity='enter' )ENTER
LEFT JOIN(
SElect
code,timedate AS ENDtime
from cte where Activity<>Nextevent
and Activity<>'Enter' and Nextevent='N/A')exits
ON enter.code=exits.code
group by enter.code,exits.ENDtime
)V
May 19, 2016 at 9:47 am
Ok, here is my stab at it.
Notice I used minutes just because it was easier for me to verify the results but you can change it to whatever time unit you want. This also assumes there will at least ONE enter for each exit or vise versa.
DECLARE @TimeCard TABLE ([IO] SMALLINT, Code INT, OPR INT, Activity VARCHAR(15), ActDate DATETIME)
INSERT INTO @TimeCard
VALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 08:14:17.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:24:23.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:04.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:39.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:33:22.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:02:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:48:48.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 18:11:32.000')
--SELECT * FROM @TimeCard
IF OBJECT_ID('tempdb..#WorkTable', 'U') IS NOT NULL
DROP TABLE #WorkTable
--Remove extra Enters and Exits add time in minutes
SELECT
[IO],
Code,
OPR,
Activity,
ActDate,
ISNULL(DATEDIFF(mi, LAG(ActDate) OVER (PARTITION BY OPR ORDER BY ActDate), ActDate), 0) AS TimeMin
INTO #WorkTable
FROM
(
SELECT
*,
ISNULL(LAG(Activity) OVER (PARTITION BY OPR ORDER BY ActDate), 'NONE') AS PrevActivity
FROM @TimeCard
) x
WHERE Activity <> PrevActivity
--SELECT * FROM #WorkTable
SELECT
Code,
Min(ActDate) AS StartTime,
Max(ActDate) AS EndTime,
DATEDIFF(mi, Min(ActDate), MAX(ActDate)) AS TotalMin,
WorkTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'EXITS'),
BreakTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'ENTER')
FROM
#WorkTable
GROUP BY
Code
May 19, 2016 at 10:15 am
Thank a Ton for solving this. This looks perfect. 🙂
May 19, 2016 at 10:34 am
navya8877 (5/19/2016)
Thank a Ton for solving this. This looks perfect. 🙂
have you tested with more than one employee?
INSERT INTO @TimeCard
VALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665158, 469, 'ENTER', '2016-05-17 15:48:48.000'),
(5, 6665158, 469, 'EXITS', '2016-05-17 18:11:32.000')
dont think the worktime and breaktime work correctly
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2016 at 10:56 am
HI Livingston
I tested with other employer also it is working find. Thanks for checking.
May 19, 2016 at 11:49 am
navya8877 (5/19/2016)
HI LivingstonI tested with other employer also it is working find. Thanks for checking.
No J is right, the break time and work time are off if you introduce another code/opr.
Not a difficult change...just need to tweak it a bit.
DECLARE @TimeCard TABLE ([IO] SMALLINT, Code INT, OPR INT, Activity VARCHAR(15), ActDate DATETIME)
INSERT INTO @TimeCard
VALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 08:14:17.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:24:23.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:04.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 12:26:39.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:33:22.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:02:43.000'),
(5, 6665157, 468, 'ENTER', '2016-05-17 13:48:48.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 18:11:32.000'),
(5, 6665158, 469, 'ENTER', '2016-05-17 15:48:48.000'),
(5, 6665158, 469, 'EXITS', '2016-05-17 18:11:32.000')
--SELECT * FROM @TimeCard
IF OBJECT_ID('tempdb..#WorkTable', 'U') IS NOT NULL
DROP TABLE #WorkTable
--Remove extra Enters and Exits add time in minutes
SELECT
[IO],
Code,
OPR,
Activity,
ActDate,
ISNULL(DATEDIFF(mi, LAG(ActDate) OVER (PARTITION BY Code ORDER BY ActDate), ActDate), 0) AS TimeMin
INTO #WorkTable
FROM
(
SELECT
*,
ISNULL(LAG(Activity) OVER (PARTITION BY Code ORDER BY ActDate), 'NONE') AS PrevActivity
FROM @TimeCard
) x
WHERE Activity <> PrevActivity
--SELECT * FROM #WorkTable
SELECT
w.Code,
Min(w.ActDate) AS StartTime,
Max(w.ActDate) AS EndTime,
DATEDIFF(mi, Min(w.ActDate), MAX(w.ActDate)) AS TotalMin,
WorkTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'EXITS' AND Code = w.Code),
BreakTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'ENTER' AND Code = w.Code)
FROM
#WorkTable w
GROUP BY
w.Code
Thanks for the catch J!
May 19, 2016 at 11:51 am
J Livingston SQL (5/19/2016)
navya8877 (5/19/2016)
Thank a Ton for solving this. This looks perfect. 🙂have you tested with more than one employee?
INSERT INTO @TimeCardVALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665158, 469, 'ENTER', '2016-05-17 15:48:48.000'),
(5, 6665158, 469, 'EXITS', '2016-05-17 18:11:32.000')
dont think the worktime and breaktime work correctly
sorry I disgaree with you
DECLARE @TimeCard TABLE ([IO] SMALLINT, Code INT, OPR INT, Activity VARCHAR(15), ActDate DATETIME)
INSERT INTO @TimeCard
VALUES
(5, 6665157, 468, 'ENTER', '2016-05-17 08:13:43.000'),
(5, 6665157, 468, 'EXITS', '2016-05-17 12:14:49.000'),
(5, 6665158, 469, 'ENTER', '2016-05-17 15:48:48.000'),
(5, 6665158, 469, 'EXITS', '2016-05-17 18:11:32.000')
IF OBJECT_ID('tempdb..#WorkTable', 'U') IS NOT NULL DROP TABLE #WorkTable
SELECT
[IO],
Code,
OPR,
Activity,
ActDate,
ISNULL(DATEDIFF(mi, LAG(ActDate) OVER (PARTITION BY OPR ORDER BY ActDate), ActDate), 0) AS TimeMin
INTO #WorkTable
FROM
(SELECT*,
ISNULL(LAG(Activity) OVER (PARTITION BY OPR ORDER BY ActDate), 'NONE') AS PrevActivity
FROM @TimeCard
) x
WHERE Activity <> PrevActivity
SELECT
Code,
Min(ActDate) AS StartTime,
Max(ActDate) AS EndTime,
DATEDIFF(mi, Min(ActDate), MAX(ActDate)) AS TotalMin,
WorkTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'EXITS'),
BreakTimeMin = (SELECT SUM(TimeMin) FROM #WorkTable WHERE Activity = 'ENTER')
FROM
#WorkTable
GROUP BY
Code
delivers this result:
+-----------------------------------------------------------------------+
¦ Code ¦ StartTime ¦ EndTime ¦ TotalMin ¦ WorkTimeMin ¦ BreakTimeMin ¦
¦---------+-----------+---------+----------+-------------+--------------¦
¦ 6665157 ¦ 13:43.0 ¦ 14:49.0 ¦ 241 ¦ 384 ¦ 0 ¦
¦---------+-----------+---------+----------+-------------+--------------¦
¦ 6665158 ¦ 48:48.0 ¦ 11:32.0 ¦ 143 ¦ 384 ¦ 0 ¦
¦---------+-----------+---------+----------+-------------+--------------¦
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2016 at 8:56 pm
There is no way that I'd auto-correct anything having to do with payroll. There's something wrong with a payroll system that allows "double punching" and there's something wrong with people's heads to not follow "the rules" to get paid. Those two things are what need to be fixed. Doing such "fixes" in code will always be error prone and invite trouble from the folks on the payroll. If something doesn't show up correctly, they should be called to HR for a correction. Whether or not they get paid for that time would be up to company policy.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2016 at 6:44 am
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 ReaderEXITS
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 ReaderEXITS
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
66651572016-05-17 08:13:43.000North Main Entry DoorsENTER
66651572016-05-17 08:14:17.000IT Hallway ENTER
66651572016-05-17 12:14:49.000Facility Door EXITS
66651572016-05-17 12:24:23.000North Main Passback ReaderEXITS
66651572016-05-17 12:26:04.000North Main Entry DoorsENTER
66651572016-05-17 12:26:39.000IT Hallway ENTER
66651572016-05-17 12:33:22.000Facility Door EXITS
66651572016-05-17 13:02:43.000IT Hallway ENTER
66651572016-05-17 13:48:48.000Tech Room ENTER
66651572016-05-17 18:11:32.000North Main Passback ReaderEXITS
May 20, 2016 at 7:43 am
Savya (5/20/2016)
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 createdby 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 ReaderEXITS
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 ReaderEXITS
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
66651572016-05-17 08:13:43.000North Main Entry DoorsENTER
66651572016-05-17 08:14:17.000IT Hallway ENTER
66651572016-05-17 12:14:49.000Facility Door EXITS
66651572016-05-17 12:24:23.000North Main Passback ReaderEXITS
66651572016-05-17 12:26:04.000North Main Entry DoorsENTER
66651572016-05-17 12:26:39.000IT Hallway ENTER
66651572016-05-17 12:33:22.000Facility Door EXITS
66651572016-05-17 13:02:43.000IT Hallway ENTER
66651572016-05-17 13:48:48.000Tech Room ENTER
66651572016-05-17 18:11:32.000North Main Passback ReaderEXITS
Ummmm...that information makes a huge difference. The IO's in the previous post were all the same. Seems to me they should have had unique ID's since they are different doors. Also you need to identify the doors that qualify for work/leaving.
May 20, 2016 at 8:23 am
Io is same..IOname is different
May 20, 2016 at 8:45 am
Either way I think you have everything you need. Rather than 'remove' the extra enter/exit just filter them out by name in your WHERE clause.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply