sql-server - 动态设置一天的开始和结束时间

Question

我有这张表，我在其中存储 TimeIn 和 Time Out of Employee。当我得到任何员工在某一天工作的总小时数时，它在日期方面工作得很好。但在外部组织中，问题是从早上 6 点到凌晨 5:59（第二天）考虑一天。

这是我的表格和示例数据。

CREATE TABLE [dbo].[Attendance]
(
    [Employee] [varchar](50) NULL,
    [TimeIn] [datetime] NULL,
    [TimeOut] [datetime] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Attendance] ([Employee], [TimeIn], [TimeOut]) VALUES (N'Lewis', CAST(N'2018-12-01 06:30:00.000' AS DateTime), CAST(N'2018-12-01 18:22:00.000' AS DateTime))
GO
INSERT [dbo].[Attendance] ([Employee], [TimeIn], [TimeOut]) VALUES (N'Lewis', CAST(N'2018-12-01 20:12:00.000' AS DateTime), CAST(N'2018-12-01 23:50:00.000' AS DateTime))
GO
INSERT [dbo].[Attendance] ([Employee], [TimeIn], [TimeOut]) VALUES (N'Lewis', CAST(N'2018-12-02 00:12:00.000' AS DateTime), CAST(N'2018-12-02 04:50:00.000' AS DateTime))
GO
INSERT [dbo].[Attendance] ([Employee], [TimeIn], [TimeOut]) VALUES (N'Lewis', CAST(N'2018-12-02 07:21:00.000' AS DateTime), CAST(N'2018-12-02 19:54:00.000' AS DateTime))
GO

这是我正在执行的查询的查询和输出。

SELECT Employee, CAST(COALESCE(TimeIn, TimeOut) AS DATE) DATE, DATEDIFF(HOUR, MIN(TimeIn), MAX(TimeOut)) [Hours Worked]
FROM [dbo].[Attendance]
GROUP BY Employee, CAST(COALESCE(TimeIn, TimeOut) AS DATE)

输出：

Employee          DATE         Hours Worked
----------------- ----------   ------------
Lewis             2018-12-01   17
Lewis             2018-12-02   19

我想要的是计算从早上 6 点到第二天早上 5:59 的工作时间。所以预期的输出如下：

Employee          DATE         Hours Worked
----------------- ----------   ------------
Lewis             2018-12-01   22:20
Lewis             2018-12-02   12:33

希望这是可能的..

标签： sql-server

Answer 1

You should probably have a calendar table which contains all the dates which you want to appear in your report. In the absence of that, we can just assume that all dates are covered by the time, and we can group by the time in, shifted earlier by 6 hours. The trick here is that we can shift all times backwards by 6 hours, to align everything with the usual 24 hour day. Something like this should work:

SELECT
    Employee,
    CONVERT(date, DATEADD(HOUR, -6, TimeIn)) AS DATE,
    CONVERT(VARCHAR(10), DATEDIFF(HOUR, MIN(TimeIn), MAX(TimeOut))) + ':' +
        CONVERT(VARCHAR(10), DATEDIFF(MINUTE, MIN(TimeIn), MAX(TimeOut)) % 60) AS [Hours Worked]
FROM Attendance
GROUP BY
    Employee,
    CONVERT(date, DATEADD(HOUR, -6, TimeIn));

Demo