如何参照签入和第二天 checkout 时间在sql server中使用日期和时间获取总工作时间

toe95027 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(229)

我有这张table，我想有时间进出

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

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

sql sql-server c#

来源：https://stackoverflow.com/questions/62832366/how-to-get-total-no-of-working-hours-with-date-time-in-sql-server-with-referen

1条答案

按热度按时间

68de4m5k1#

如果您只想将“奇数行”和“偶数行”组合起来，那么可以使用 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 );

赞(0）回复(0）举报 2021-07-24

我来回答

如何参照签入和第二天 checkout 时间在sql server中使用日期和时间获取总工作时间

1条答案

相关问题

热门标签

最新问答