如何参考签入和第二天结帐时间在SQL Server中获取带有日期和时间的总工作时间

Question

我有这个表，我想得到time_in和time_out

BADGENUMBER CHECKTIME   SENSORID
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-25 07:03:19.000 1
172 2020-06-25 18:53:55.000 1
172 2020-06-26 05:17:45.000 1
172 2020-06-28 19:09:34.000 1
172 2020-06-29 05:00:07.000 1
172 2020-06-29 05:00:09.000 1
172 2020-06-16 19:24:49.000 1
172 2020-06-17 05:30:03.000 1
172 2020-06-17 19:41:17.000 1
172 2020-06-18 08:07:51.000 1
172 2020-06-18 19:10:57.000 1
172 2020-06-19 05:25:12.000 1
172 2020-06-20 19:06:06.000 1
172 2020-06-21 05:11:24.000 1
172 2020-06-21 05:11:27.000 1
172 2020-06-21 18:55:04.000 1
172 2020-06-22 06:07:48.000 1
172 2020-06-22 18:34:48.000 1
172 2020-06-23 05:00:31.000 1
172 2020-06-23 18:57:27.000 1
172 2020-06-23 18:57:29.000 1
172 2020-06-24 07:06:40.000 1
172 2020-06-24 19:01:12.000 1
172 2020-06-24 19:01:20.000 1
172 2020-06-25 07:03:19.000 1
172 2020-06-25 18:53:55.000 1
172 2020-06-26 05:17:45.000 1
172 2020-06-28 19:09:34.000 1
172 2020-06-29 05:00:07.000 1
172 2020-06-29 05:00:09.000 1
172 2020-06-29 19:08:50.000 1
172 2020-06-30 05:23:19.000 1
172 2020-06-30 18:56:33.000 1

如果存在，我想得到第二天的进出时间

Answer 1

如果您只想将"奇数行"；用"；偶数行"；，可以使用row_number():

select badgenumber, min(checktime), max(checktime)
from (select t.*, row_number() over (partition by badgenumber order by checktime) as seqnum
from t
) t
group by floor( (seqnum - 1) / 2 );