CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Dynamically set the Start and End time of Day

I have this table in which I am storing TimeIn and Time Out of Employee. When I get Total Hours any Employee have worked in certain day, it works fine date wise. But in out organization the issue is that a day is considered from 6 AM till 5:59 AM (next day).

Here is my table and sample data.

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

Here is the query and output of the query I am executing.

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)

Output:

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

What I want is to get the working hours calculated from 6 AM to 5:59 AM next day. So the expected output is as below:

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

Hope this is possible..

Upvotes: 2

Views: 140

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

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));

enter image description here

Demo

Upvotes: 3

Related Questions