Karthikeyan.V Karthikeyan.V - 4 months ago 13
SQL Question

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 ║ ║
╚═════════╩════════╩═════════════════════╩═══════════╩══════════╩══════════╩══════════════════╝

Answer

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