API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 19th, 2019
92
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.55 KB | None | 0 0
raw download clone embed print report
  1. INSERT INTO `attendancemachinelogin` (`AttendanceMachineLoginId`, `EmpId`, `TimeTrackId`, `AttendanceDateTime`, `RecordAddDateTime`) VALUES
  2. (0, 81, 315079, '2018-8-15 14:8:46', '2018-08-15 14:09:25'),
  3. (0, 81, 315079, '2018-8-15 14:20:38', '2018-08-15 14:21:17'),
  4. (0, 81, 315079, '2018-8-15 14:21:9', '2018-08-15 14:21:47'),
  5. (0, 81, 315079, '2018-8-15 14:28:37', '2018-08-15 14:29:16'),
  6. (0, 81, 315079, '2018-8-15 14:28:58', '2018-08-15 14:29:36'),
  7. (0, 81, 315079, '2018-8-15 14:36:42', '2018-08-15 14:37:21'),
  8. (0, 81, 315079, '2018-8-15 15:36:34', '2018-08-15 15:37:13'),
  9. (0, 81, 315079, '2018-8-15 15:52:39', '2018-08-15 15:53:17'),
  10. (0, 81, 315079, '2018-8-15 16:5:38', '2018-08-15 16:06:17'),
  11. (0, 81, 315079, '2018-8-15 16:6:50', '2018-08-15 16:07:29'),
  12. (0, 81, 315079, '2018-8-15 16:8:49', '2018-08-15 16:09:29'),
  13. (0, 81, 315079, '2018-8-15 16:18:28', '2018-08-15 16:19:08'),
  14. (0, 81, 315079, '2018-8-15 16:20:49', '2018-08-15 16:21:28'),
  15. (0, 81, 315079, '2018-8-15 16:23:18', '2018-08-15 16:23:58'),
  16. (0, 81, 315079, '2018-8-15 16:24:3', '2018-08-15 16:24:42'),
  17. (0, 81, 315079, '2018-8-15 16:24:47', '2018-08-15 16:25:26'),
  18. (0, 81, 315079, '2018-8-15 16:24:58', '2018-08-15 16:25:37'),
  19. (0, 81, 315079, '2018-8-15 16:25:54', '2018-08-15 16:26:33'),
  20. (0, 81, 315079, '2018-8-15 16:56:47', '2018-08-15 16:57:27'),
  21. (0, 101, 417092, '2018-8-15 17:37:53', '2018-08-15 17:38:32'),
  22. (0, 101, 417092, '2018-8-15 18:4:34', '2018-08-15 18:05:14'),
  23. (0, 101, 417092, '2018-8-15 18:7:43', '2018-08-15 18:08:22'),
  24. (0, 81, 315079, '2018-8-15 18:13:15', '2018-08-15 18:13:54'),
  25. (0, 81, 315079, '2018-8-17 10:50:16', '2018-08-17 10:50:54'),
  26. (0, 101, 417092, '2018-8-17 10:51:54', '2018-08-17 10:52:31'),
  27. (0, 4, 413034, '2018-8-17 11:45:16', '2018-08-17 11:45:54'),
  28. (0, 91, 916086, '2018-8-17 11:59:34', '2018-08-17 12:00:12'),
  29. (0, 81, 315079, '2018-8-17 12:0:19', '2018-08-17 12:00:56'),
  30. (0, 81, 315079, '2018-8-17 15:7:41', '2018-08-17 15:08:17'),
  31. (0, 101, 417092, '2018-8-17 15:9:54', '2018-08-17 15:10:32'),
  32. (0, 101, 417092, '2018-8-17 15:10:9', '2018-08-17 15:10:45'),
  33. (0, 101, 417092, '2018-8-17 15:10:23', '2018-08-17 15:10:59'),
  34. (0, 101, 417092, '2018-8-17 15:10:25', '2018-08-17 15:11:02'),
  35. (0, 101, 417092, '2018-8-17 15:11:6', '2018-08-17 15:11:43'),
  36. (0, 101, 417092, '2018-8-17 15:11:15', '2018-08-17 15:11:52'),
  37. (0, 101, 417092, '2018-8-17 15:11:17', '2018-08-17 15:11:54'),
  38. (0, 81, 315079, '2018-8-17 15:11:32', '2018-08-17 15:12:09'),
  39. (0, 81, 315079, '2018-8-17 15:12:32', '2018-08-17 15:13:09'),
  40. (0, 81, 315079, '2018-8-17 15:35:33', '2018-08-17 15:36:10'),
  41. (0, 81, 315079, '2018-8-17 15:41:58', '2018-08-17 15:42:34'),
  42. (0, 81, 315079, '2018-8-17 15:42:17', '2018-08-17 15:42:54'),
  43. (0, 81, 315079, '2018-8-17 16:8:25', '2018-08-17 16:09:01'),
  44. (0, 81, 315079, '2018-8-17 16:8:32', '2018-08-17 16:09:08'),
  45. (0, 101, 417092, '2018-8-17 16:8:53', '2018-08-17 16:09:30'),
  46. (0, 101, 417092, '2018-8-17 16:9:20', '2018-08-17 16:09:57'),
  47. (0, 4, 413034, '2018-8-17 16:10:16', '2018-08-17 16:10:53'),
  48. (0, 36, 413037, '2018-8-17 16:10:46', '2018-08-17 16:11:23'),
  49. (0, 81, 315079, '2018-8-17 16:22:21', '2018-08-17 16:22:58'),
  50. (0, 101, 417092, '2018-8-17 16:22:45', '2018-08-17 16:23:21'),
  51. (0, 4, 413034, '2018-8-17 16:23:12', '2018-08-17 16:23:49'),
  52. (0, 81, 315079, '2018-8-17 16:23:35', '2018-08-17 16:24:12'),
  53. (0, 81, 315079, '2018-8-17 16:44:4', '2018-08-17 16:44:42'),
  54. (0, 101, 417092, '2018-8-17 16:44:22', '2018-08-17 16:44:58'),
  55. (0, 81, 315079, '2018-8-17 17:6:51', '2018-08-17 17:07:28'),
  56. (0, 101, 417092, '2018-8-17 17:7:8', '2018-08-17 17:07:45'),
  57. (0, 4, 413034, '2018-8-17 17:7:52', '2018-08-17 17:08:28'),
  58. (0, 81, 315079, '2018-8-17 17:9:25', '2018-08-17 17:10:02'),
  59. (0, 101, 417092, '2018-8-17 17:9:46', '2018-08-17 17:10:22'),
  60. (0, 4, 413034, '2018-8-17 17:10:6', '2018-08-17 17:10:42'),
  61. (0, 81, 315079, '2018-8-17 17:10:24', '2018-08-17 17:11:01'),
  62. (0, 81, 315079, '2018-8-17 17:10:39', '2018-08-17 17:11:15'),
  63. (0, 101, 417092, '2018-8-17 17:10:47', '2018-08-17 17:11:24'),
  64. (0, 101, 417092, '2018-8-17 17:10:58', '2018-08-17 17:11:35'),
  65. (0, 81, 315079, '2018-8-17 17:11:10', '2018-08-17 17:11:46'),
  66. (0, 101, 417092, '2018-8-17 17:11:31', '2018-08-17 17:12:09'),
  67. (0, 4, 413034, '2018-8-17 17:40:40', '2018-08-17 17:41:18'),
  68. (0, 101, 417092, '2018-8-17 17:41:23', '2018-08-17 17:41:59'),
  69. (0, 36, 413037, '2018-8-17 17:41:37', '2018-08-17 17:42:14'),
  70. (0, 81, 315079, '2018-8-17 17:42:9', '2018-08-17 17:42:45'),
  71. (0, 3, 213020, '2018-8-17 17:47:34', '2018-08-17 17:48:11'),
  72. (0, 81, 315079, '2018-8-17 17:48:16', '2018-08-17 17:48:52'),
  73. (0, 4, 413034, '2018-8-17 17:48:59', '2018-08-17 17:49:36'),
  74. (0, 4, 413034, '2018-8-17 17:49:59', '2018-08-17 17:50:36'),
  75. (0, 36, 413037, '2018-8-17 17:52:36', '2018-08-17 17:53:13'),
  76. (0, 101, 417092, '2018-8-17 17:52:53', '2018-08-17 17:53:29'),
  77. (0, 6, 213016, '2018-8-17 17:53:30', '2018-08-17 17:54:06'),
  78. (0, 81, 315079, '2018-8-17 17:53:44', '2018-08-17 17:54:20'),
  79. (0, 4, 413034, '2018-8-17 17:54:27', '2018-08-17 17:55:03'),
  80. (0, 3, 213020, '2018-8-17 17:54:49', '2018-08-17 17:55:27'),
  81. (0, 4, 413034, '2018-8-17 17:55:23', '2018-08-17 17:56:00'),
  82. (0, 36, 413037, '2018-8-17 17:58:33', '2018-08-17 17:59:10'),
  83. (0, 101, 417092, '2018-8-17 17:58:47', '2018-08-17 17:59:24'),
  84. (0, 102, 517094, '2018-8-17 17:59:4', '2018-08-17 17:59:40'),
  85. (0, 81, 315079, '2018-8-17 17:59:33', '2018-08-17 18:00:09'),
  86. (0, 4, 413034, '2018-8-17 18:0:16', '2018-08-17 18:00:52'),
  87. (0, 3, 213020, '2018-8-17 18:0:40', '2018-08-17 18:01:17'),
  88. (0, 6, 213016, '2018-8-17 18:1:30', '2018-08-17 18:02:06'),
  89. (0, 36, 413037, '2018-8-17 18:26:24', '2018-08-17 18:27:01'),
  90. (0, 101, 417092, '2018-8-17 18:26:38', '2018-08-17 18:27:14'),
  91. (0, 6, 213016, '2018-8-17 18:27:9', '2018-08-17 18:27:45'),
  92. (0, 81, 315079, '2018-8-17 18:27:24', '2018-08-17 18:28:00'),
  93. (0, 102, 517094, '2018-8-17 18:27:38', '2018-08-17 18:28:14'),
  94. (0, 4, 413034, '2018-8-17 18:28:13', '2018-08-17 18:28:49'),
  95. (0, 81, 315079, '2018-8-17 19:36:49', '2018-08-17 19:37:26'),
  96. (0, 101, 417092, '2018-8-17 19:37:17', '2018-08-17 19:37:54'),
  97. (0, 102, 517094, '2018-8-17 19:37:30', '2018-08-17 19:38:07'),
  98. (0, 36, 413037, '2018-8-17 19:38:13', '2018-08-17 19:38:50'),
  99. (0, 4, 413034, '2018-8-17 19:38:54', '2018-08-17 19:39:32'),
  100. (0, 3, 213020, '2018-8-17 19:39:58', '2018-08-17 19:40:35'),
  101. (0, 101, 417092, '2018-8-18 10:21:26', '2018-08-18 10:22:03'),
  102. (0, 81, 315079, '2018-8-18 10:30:23', '2018-08-18 10:31:09'),
  103. (0, 4, 413034, '2018-8-18 10:31:46', '2018-08-18 10:32:27'),
  104. (0, 102, 517094, '2018-8-18 10:32:15', '2018-08-18 10:32:53'),
  105. (0, 6, 213016, '2018-8-18 10:32:44', '2018-08-18 10:33:22'),
  106. (0, 3, 213020, '2018-8-18 10:33:23', '2018-08-18 10:34:03'),
  107. (0, 81, 315079, '2018-8-18 10:42:49', '2018-08-18 10:43:27'),
  108. (0, 101, 417092, '2018-8-18 10:43:25', '2018-08-18 10:44:03'),
  109. (0, 81, 315079, '2018-8-18 10:48:51', '2018-08-18 10:49:30'),
  110. (0, 102, 517094, '2018-8-18 10:49:9', '2018-08-18 10:49:49'),
  111. (0, 81, 315079, '2018-8-18 10:56:46', '2018-08-18 10:57:25'),
  112. (0, 1, 1211003, '2018-8-18 10:57:0', '2018-08-18 10:57:38'),
  113. (0, 4, 413034, '2018-8-18 10:57:51', '2018-08-18 10:58:38'),
  114. (0, 3, 213020, '2018-8-18 10:58:43', '2018-08-18 10:59:26');
  115.  
  116. WITH cteB (EID, mins) AS
  117. (
  118. WITH cteA (EID, TS, cnt) AS
  119. (
  120. SELECT w.EmpId
  121. , w.AttendanceDateTime
  122. , IF( @prevEID != @prevID := w.EmpID, @cnt := 1, @cnt := @cnt+1 ) AS cnt
  123. FROM table AS w
  124. ORDER BY w.EmpId ASC
  125. , w.AttendanceDateTime ASC
  126. )
  127. SELECT z.EID
  128. , TIMESTAMPDIFF(MINUTE, z.TS, q.TS) AS mins
  129. FROM cteA AS z
  130. JOIN cteA AS q ON q.EID = z.EID
  131. AND z.cnt = q.cnt+1
  132. AND z.cnt % 2 = 1
  133. )
  134. SELECT x.EID AS EmpID
  135. , SUM(x.mins) AS Eminutes
  136. FROM cteB AS x
  137. GROUP BY x.EID
  138. ORDER BY x.EID ASC
  139. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!