Karthikeyan Vedi
Reputation: 1360
Calculate In Duration and Out Duration of Employee
I Have a table named DeviceLogs_8_2016 in which all records of In-time and out-time record of employees are stored.
Table name changes every month.
Ex: The records from 2016-09-01 0:00:00 would be saved in table named DeviceLogs_9_2016
Table Contains records of all employees such as below, but I need only for a single UserId on a Specified Date.
+--------+---------------------+-----------+
| UserId | LogDate | Direction |
+--------+---------------------+-----------+
| 7034 | 2016-08-08 08:21:14 | in |
| 5012 | 2016-08-08 08:21:26 | out |
| 7036 | 2016-08-08 08:21:34 | in |
| 7034 | 2016-08-08 10:01:14 | in |
| 8015 | 2016-08-08 10:10:39 | in |
| 2055 | 2016-08-08 10:11:27 | in |
| 209 | 2016-08-08 11:28:25 | out |
| 209 | 2016-08-08 11:32:32 | in |
| 11253 | 2016-08-08 12:35:17 | out |
| 7034 | 2016-08-08 12:37:58 | in |
| 7034 | 2016-08-08 13:30:13 | out |
| 4586 | 2016-08-08 13:30:24 | in |
| 7034 | 2016-08-08 13:30:28 | out |
| 209 | 2016-08-08 13:35:36 | out |
+--------+---------------------+-----------+
Sample Data with only single UserID for the Whole Day is as given Below
+--------+---------------------+-----------+
| UserId | LogDate | Direction |
+--------+---------------------+-----------+
| 7034 | 2016-08-08 08:20:59 | in |
| 7034 | 2016-08-08 08:21:04 | in |
| 7034 | 2016-08-08 08:21:14 | in |
| 7034 | 2016-08-08 08:21:26 | out |
| 7034 | 2016-08-08 08:21:34 | in |
| 7034 | 2016-08-08 09:35:26 | out |
| 7034 | 2016-08-08 10:01:14 | in |
| 7034 | 2016-08-08 12:35:17 | out |
| 7034 | 2016-08-08 12:37:58 | in |
| 7034 | 2016-08-08 13:29:13 | out |
| 7034 | 2016-08-08 13:30:08 | out |
| 7034 | 2016-08-08 13:30:13 | out |
| 7034 | 2016-08-08 14:30:24 | in |
| 7034 | 2016-08-08 17:30:24 | out |
| 7034 | 2016-08-08 17:40:24 | in |
| 7034 | 2016-08-08 22:15:38 | out |
| 7034 | 2016-08-08 22:50:05 | in |
| 7034 | 2016-08-09 01:20:05 | out |
| 7034 | 2016-08-09 01:22:10 | in |
| 7034 | 2016-08-09 04:50:15 | out |
+--------+---------------------+-----------+
Considering the Attendance of that day begins from 2016-08-08 05:00:00 and Ends at 2016-08-09 05:00:00.
I want to calculate the Total In-Duration and Total Out-Duration of the employee on that day. if In or Out appears continuously then we can only consider the last In before Out and the First Out before In.
I want result to be like
+--------+------------+-------------+--------------+
| UserId | LogDate | In_Duration | Out_Duration |
+--------+------------+-------------+--------------+
| 7034 | 2016-08-08 | 18:12:41 | 02:16:20 |
+--------+------------+-------------+--------------+
I have worked out the calculations in an excel sheet for the specified sample data.
╔═════════╦════════╦═════════════════════╦═══════════╦══════════╦══════════╦══════════════════╗
║ row_sno ║ UserId ║ LogDate ║ Direction ║ In_Diff ║ Out_Diff ║ Diff_Calculation ║
╠═════════╬════════╬═════════════════════╬═══════════╬══════════╬══════════╬══════════════════╣
║ 1 ║ 7034 ║ 2016-08-08 08:20:59 ║ in ║ ║ ║ ║
║ 2 ║ 7034 ║ 2016-08-08 08:21:04 ║ in ║ ║ ║ ║
║ 3 ║ 7034 ║ 2016-08-08 08:21:14 ║ in ║ ║ ║ ║
║ 4 ║ 7034 ║ 2016-08-08 08:21:26 ║ out ║ 00:00:12 ║ ║ 4th - 3th row ║
║ 5 ║ 7034 ║ 2016-08-08 08:21:34 ║ in ║ ║ 00:00:08 ║ 5th - 4th row ║
║ 6 ║ 7034 ║ 2016-08-08 09:35:26 ║ out ║ 01:13:52 ║ ║ 6th - 5th row ║
║ 7 ║ 7034 ║ 2016-08-08 10:01:14 ║ in ║ ║ 00:25:48 ║ 7th - 6th row ║
║ 8 ║ 7034 ║ 2016-08-08 12:35:17 ║ out ║ 02:34:03 ║ ║ 8th - 7th row ║
║ 9 ║ 7034 ║ 2016-08-08 12:37:58 ║ in ║ ║ 00:02:41 ║ 9th - 8th row ║
║ 10 ║ 7034 ║ 2016-08-08 13:29:13 ║ out ║ 00:51:15 ║ ║ 10th - 9th row ║
║ 11 ║ 7034 ║ 2016-08-08 13:30:08 ║ out ║ ║ ║ ║
║ 12 ║ 7034 ║ 2016-08-08 13:30:13 ║ out ║ ║ ║ ║
║ 13 ║ 7034 ║ 2016-08-08 14:30:24 ║ in ║ ║ 01:01:11 ║ 13th - 10th row ║
║ 14 ║ 7034 ║ 2016-08-08 17:30:24 ║ out ║ 03:00:00 ║ ║ 14th - 13th row ║
║ 15 ║ 7034 ║ 2016-08-08 17:40:24 ║ in ║ ║ 00:10:00 ║ 15th - 14th row ║
║ 16 ║ 7034 ║ 2016-08-08 22:15:38 ║ out ║ 04:35:14 ║ ║ 16th - 15th row ║
║ 17 ║ 7034 ║ 2016-08-08 22:50:05 ║ in ║ ║ 00:34:27 ║ 17th - 16th row ║
║ 18 ║ 7034 ║ 2016-08-09 01:20:05 ║ out ║ 02:30:00 ║ ║ 18th - 17th row ║
║ 19 ║ 7034 ║ 2016-08-09 01:22:10 ║ in ║ ║ 00:02:05 ║ 19th - 18th row ║
║ 20 ║ 7034 ║ 2016-08-09 04:50:15 ║ out ║ 03:28:05 ║ ║ 20th - 19th row ║
║ ║ ║ ║ ║ ║ ║ ║
║ ║ ║ ║ TOTAL ║ 18:12:41 ║ 02:16:20 ║ ║
╚═════════╩════════╩═════════════════════╩═══════════╩══════════╩══════════╩══════════════════╝
Upvotes: 0
Views: 277
Answers (3)
MtwStark
Reputation: 4058
this query gives the expected result using LAG() function (faster):
;with
q0 as (
SELECT
UserId, LogDate, Direction,
LAG(Direction) OVER (partition by userid ORDER BY LogDate) AS PreDirection
-- comment/uncomment below line to calc respectively from the first or the last `[in]` after `[out]`
-- comment = first, uncomment = last
, LAG(LogDate) OVER (partition by userid ORDER BY LogDate) AS PreLogDate
FROM DeviceLogs_8_2016
),
q as (
select
UserId, LogDate, Direction,
-- rem/un-rem to calc respectively from the first or the last `[in]` after `[out]`
/* use this for first */ -- datediff(second, lag(LogDate) over (partition by userid order by logdate), LogDate) secs
/* use this for last */ datediff(second, PreLogDate, LogDate) secs
from q0
where Direction<>PreDirection or PreDirection is null
),
s as (
select *
from q
pivot ( SUM(secs) for Direction in ([in], [out])) as p
)
--/*DEBUG*/ select *, convert(time(0), dateadd(second, secs, 0) ) duration from q order by userid, LogDate
select
UserId,
convert(time(0), dateadd(second, SUM([out]), 0) ) In_Duration,
convert(time(0), dateadd(second, SUM([in]), 0) ) Out_Duration
from s
group by userid
result:
UserId In_Duration Out_Duration
209 03:17:16 00:33:24
7034 18:12:41 02:15:20
Upvotes: 1
neer
Reputation: 4092
Try this
DECLARE @Tbl TABLE (UserId INT, LogDate DATETIME, Direction NVARCHAR(50))
INSERT INTO @Tbl
VALUES
(7034 ,'2016-08-08 08:20:59', 'in' ),
(7034 ,'2016-08-08 08:21:04', 'in' ),
(7034 ,'2016-08-08 08:21:14', 'in' ),
(7034 ,'2016-08-08 08:21:26', 'out'),
(7034 ,'2016-08-08 08:21:34', 'in' ),
(7034 ,'2016-08-08 09:35:26', 'out'),
(7034 ,'2016-08-08 10:01:14', 'in' ),
(7034 ,'2016-08-08 12:35:17', 'out'),
(7034 ,'2016-08-08 12:37:58', 'in' ),
(7034 ,'2016-08-08 13:29:13', 'out'),
(7034 ,'2016-08-08 13:30:08', 'out'),
(7034 ,'2016-08-08 13:30:13', 'out'),
(7034 ,'2016-08-08 14:30:24', 'in' ),
(7034 ,'2016-08-08 17:30:24', 'out'),
(7034 ,'2016-08-08 17:40:24', 'in' ),
(7034 ,'2016-08-08 22:15:38', 'out'),
(7034 ,'2016-08-08 22:50:05', 'in' ),
(7034 ,'2016-08-09 01:20:05', 'out'),
(7034 ,'2016-08-09 01:22:10', 'in' ),
(7034 ,'2016-08-09 04:50:15', 'out')
;WITH CTE
AS
(
SELECT
UserId ,
LogDate ,
Direction,
LAG(LogDate) OVER (ORDER BY LogDate) AS PreLogDate,
LAG(Direction) OVER (ORDER BY LogDate) AS PreDirection
FROM
@Tbl
WHERE
UserId = 7034
)
SELECT
A.UserId ,
A.LogDate ,
A.Direction ,
IIF(In_Diff <> 0,
RIGHT('0' + CAST(In_Diff / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((In_Diff / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(In_Diff % 60 AS VARCHAR),2), '') AS In_Diff ,
IIF(Out_Diff <> 0,
RIGHT('0' + CAST(Out_Diff / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((Out_Diff / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(Out_Diff % 60 AS VARCHAR),2), '') AS Out_Diff
FROM
(
SELECT
CTE.UserId ,
CTE.LogDate ,
CTE.Direction ,
IIF(CTE.Direction = 'out' AND CTE.PreDirection = 'in', DATEDIFF(SECOND, cte.PreLogDate, cte.LogDate), 0) AS In_Diff,
IIF(CTE.Direction = 'in' AND CTE.PreDirection = 'out', DATEDIFF(SECOND, cte.PreLogDate, cte.LogDate), 0) AS Out_Diff
FROM
CTE
) A
Result
UserId LogDate Direction In_Diff Out_Diff
7034 2016-08-08 08:20:59.000 in
7034 2016-08-08 08:21:04.000 in
7034 2016-08-08 08:21:14.000 in
7034 2016-08-08 08:21:26.000 out 00:00:12
7034 2016-08-08 08:21:34.000 in 00:00:08
7034 2016-08-08 09:35:26.000 out 01:13:52
7034 2016-08-08 10:01:14.000 in 00:25:48
7034 2016-08-08 12:35:17.000 out 02:34:03
7034 2016-08-08 12:37:58.000 in 00:02:41
7034 2016-08-08 13:29:13.000 out 00:51:15
7034 2016-08-08 13:30:08.000 out
7034 2016-08-08 13:30:13.000 out
7034 2016-08-08 14:30:24.000 in 01:00:11
7034 2016-08-08 17:30:24.000 out 03:00:00
7034 2016-08-08 17:40:24.000 in 00:10:00
7034 2016-08-08 22:15:38.000 out 04:35:14
7034 2016-08-08 22:50:05.000 in 00:34:27
7034 2016-08-09 01:20:05.000 out 02:30:00
7034 2016-08-09 01:22:10.000 in 00:02:05
7034 2016-08-09 04:50:15.000 out 03:28:05
Upvotes: 2
Cato
Reputation: 3701
;WITH
Punch_logs_U as (SELECT distinct userid, logdate,direction from punch_logs),
IN_OUT_TIMES
AS (SELECT P1.USERID,
P1.LOGDATE,
P1.DIRECTION,
P2.Logdate Logdate_out,
P2.Direction Direction_out
FROM Punch_Logs_u P1
OUTER APPLY
(SELECT TOP 1 PC.Logdate, PC.Direction From Punch_Logs_u PC WHERE
PC.UserId = P1.UserId
AND
PC.Direction = 'OUT'
AND PC.Logdate >= P1.Logdate) P2
WHERE P1.Direction = 'IN' AND
NOT EXISTS (SELECT 0 FROM Punch_Logs_u P3 WHERE
P3.UserID = P1.UserId
AND P3.direction = 'IN'
AND P3.logdate > P1.Logdate
AND P3.Logdate <= P2.Logdate)
)
SELECT IO.USERID,
IO.Logdate LogDate_in,
io.Logdate_out,
DATEDIFF(minute,io.logdate, io.logdate_out) TimeInMins ,
DATEDIFF(minute,LAG(logdate, 1,io.logdate) over (partition by userid order by logdate), io.logdate) TimeOUTMins
FROM IN_OUT_TIMES IO order by userid, io.logdate
Upvotes: -1
Related Questions
- Oracle query for get employee in and out time for generated query based on first in and lastout
- sql how to get first-in of employee between timein timeout
- First TimeIn Last TimeOut for each employee for respective days
- How to get all in and out time of an particular employee?
- SQL Server total Duration of value
- Count numbers of days worked for Employees in SQL Server
- Counting how many days an employee stays in a function
- Calculating total minutes of an employee
- Calculate IN/OUT by employee on time clock over SQL Server 2008
- how to Calculate Leave Duration?