SQL查询每日出勤记录

发布于 2025-01-25 22:22:06 字数 1290 浏览 1 评论 0原文

您可以帮助我使用查询转换这些数据吗？

ID	DateTime	状态
1	2022-04-01 07:38:31	时间在
1	2022-04-01 12:07:17	中断
1	2022-04-01 12:07:39	突破
1	2022-04-01 16： 43:17	超时
1	2022-04-02 07:38:31	时间在
1	2022-04-02 12:07:39	突破

ID ID	日期	日期时间	突破	超时	时间
1	2022-04-01	07:38 ：31	12:07:17	12:07:39	16:43:17
1	2022-04-02	07:38:31		12:07:39
1	2022-04-04-03
1	2022-04-04-04
1	2022-04--04--04-- 05
1	2022-04-06

表日期应为2022-04-30。

原文

Can you help me transform this data using query?

id	datetime	status
1	2022-04-01 07:38:31	Time In
1	2022-04-01 12:07:17	Break In
1	2022-04-01 12:07:39	Break Out
1	2022-04-01 16:43:17	Time Out
1	2022-04-02 07:38:31	Time In
1	2022-04-02 12:07:39	Break Out

id	date	Time In	Break In	Break Out	Time Out
1	2022-04-01	07:38:31	12:07:17	12:07:39	16:43:17
1	2022-04-02	07:38:31		12:07:39
1	2022-04-03
1	2022-04-04
1	2022-04-05
1	2022-04-06

The table date should be up to 2022-04-30.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苦行僧 2025-02-01 22:22:06

您需要使您使用的SQL类型使日期函数正确，这是T -SQL（MS SQL Server）代码：

SELECT ID, 
       convert(date,Datetime,111) as Day,
       MAX(CASE WHEN status = 'Time In' THEN Datetime ELSE NULL END) as TimeIn,
       MAX(CASE WHEN status = 'Break In' THEN Datetime ELSE NULL END) as BreakIn,
       MAX(CASE WHEN status = 'Break Out' THEN Datetime ELSE NULL END) as BreakOut,
       MAX(CASE WHEN status = 'Time Out' THEN Datetime ELSE NULL END) as TimeOut
 FROM mytest
GROUP BY ID, 
         convert(date,Datetime,111)

A建议：考虑更改DateTime列的名称作为列的名称会在某个时候回来困扰您。

另外，如果您只需要时间部分，则将任何适合您的SQL风味的逻辑应用于DateTime中的任何逻辑。

You'll need to make the date function correct for the type of SQL you're using, this is T-SQL (MS SQL Server) code:

SELECT ID, 
       convert(date,Datetime,111) as Day,
       MAX(CASE WHEN status = 'Time In' THEN Datetime ELSE NULL END) as TimeIn,
       MAX(CASE WHEN status = 'Break In' THEN Datetime ELSE NULL END) as BreakIn,
       MAX(CASE WHEN status = 'Break Out' THEN Datetime ELSE NULL END) as BreakOut,
       MAX(CASE WHEN status = 'Time Out' THEN Datetime ELSE NULL END) as TimeOut
 FROM mytest
GROUP BY ID, 
         convert(date,Datetime,111)

A suggestion: consider changing the name of the datetime column - giving a column a reserved word as a column name will come back to haunt you at some point.

Also, if you want just the time portion, then apply whatever logic is appropriate to your SQL flavor to parse that from the Datetime.

回复收藏 0 原文

~没有更多了~