sql - Calculate In Duration and Out Duration of Employee -
i have table named devicelogs_8_2016 in records of in-time , out-time record of employees stored.
table name changes every month. ex: records 2016-09-01 0:00:00 saved in table named devicelogs_9_2016
table contains records of employees such below, need single userid on 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 single userid whole day 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 attendance of day begins 2016-08-08 05:00:00 , ends @ 2016-08-09 05:00:00.
i want calculate total in-duration , total out-duration of employee on day. if in or out appears continuously can consider last in before out , first out before in.
i want result like
+--------+------------+-------------+--------------+ | userid | logdate | in_duration | out_duration | +--------+------------+-------------+--------------+ | 7034 | 2016-08-08 | 18:12:41 | 02:16:20 | +--------+------------+-------------+--------------+ i have worked out calculations in excel sheet 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 ║ ║ ╚═════════╩════════╩═════════════════════╩═══════════╩══════════╩══════════╩══════════════════╝
this query gives expected result using lag() function (faster):
;with q0 ( select userid, logdate, direction, lag(direction) on (partition userid order logdate) predirection -- comment/uncomment below line calc respectively first or last `[in]` after `[out]` -- comment = first, uncomment = last , lag(logdate) on (partition userid order logdate) prelogdate devicelogs_8_2016 ), q ( select userid, logdate, direction, -- rem/un-rem calc respectively first or last `[in]` after `[out]` /* use first */ -- datediff(second, lag(logdate) on (partition userid order logdate), logdate) secs /* use last */ datediff(second, prelogdate, logdate) secs q0 direction<>predirection or predirection null ), s ( select * q pivot ( sum(secs) direction in ([in], [out])) p ) --/*debug*/ select *, convert(time(0), dateadd(second, secs, 0) ) duration q order userid, logdate select userid, convert(time(0), dateadd(second, sum([out]), 0) ) in_duration, convert(time(0), dateadd(second, sum([in]), 0) ) out_duration s group userid result:
userid in_duration out_duration 209 03:17:16 00:33:24 7034 18:12:41 02:15:20
Comments
Post a Comment