February 24, 2018 at 6:55 am
I have a question about SQL Server: how to calculate login and logout time based on below conditions?
The same employee may work multiple shifts in the same day. logout and login time difference is more than 5 hours then consider as next shift for that employee.
Login and logout time more than 5 hours or less or more then consider same shift only for that employee
OnFloor time how much time he spend: sum(login time - logout time)
OffFloor time how much time he spend: sum(logout time - login time)
Example: emp: 101 is login time : 2018-02-06 16:03:08.000 and logout time is : 2018-02-06 22:01:40.000 then total time is : 5 hours : 38 min : 32 seconds
CREATE TABLE [dbo].[empstafflogindetails]
(
[Emp ID] [float] NULL,
[Area Of Access] [nvarchar](255) NULL,
[Time] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[empstafflogindetails] ([Emp ID], [Area Of Access], [Time])
VALUES (1, N' IN', CAST(N'2017-08-02T09:00:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-02T10:30:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-03T09:30:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-03T12:30:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-03T12:40:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-03T17:10:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-03T06:30:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-03T08:30:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-05T23:30:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-06T01:55:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-06T02:15:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-06T06:10:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-02T11:00:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-02T12:00:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-02T13:00:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-06T14:01:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-06T15:01:00.000' AS DateTime)),
(1, N' IN', CAST(N'2017-08-06T15:20:00.000' AS DateTime)),
(1, N' OUT', CAST(N'2017-08-06T20:01:00.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T16:23:49.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T16:26:01.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T15:20:07.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T16:00:07.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T16:02:02.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T22:41:40.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T22:56:33.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T22:58:28.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T22:58:32.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-05T23:00:28.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T21:47:38.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-05T23:28:33.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-06T16:03:08.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-06T22:01:40.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-06T22:42:15.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T01:32:08.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T00:33:19.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-07T00:33:39.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-07T14:57:57.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T22:34:51.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-07T23:05:13.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T23:19:57.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-07T23:24:07.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T23:31:32.000' AS DateTime)),
(101, N' IN', CAST(N'2018-02-07T23:34:12.000' AS DateTime)),
(101, N' OUT', CAST(N'2018-02-07T23:34:36.000' AS DateTime))
GO
Based on above data I want output like below :
ShiftDate |ShitStartTime |ShiftEndTime |Total_Time |OnFloor |OffFloor |Emp Id
2017-08-02 |2017-08-02 09:00:00.000 |2017-08-02 12:00:00.000 |04:00:00 |02:30:00 |01:30:00 |1
2017-08-03 |2017-08-03 06:30:00.000 |2017-08-03 17:10:00.000 |10:40:00 |09:30:00 |01:10:00 |1
2017-08-05 |2017-08-05 23:30:00.000 |2017-08-06 06:10:00.000 |06:40:00 |06:20:00 |00:20:00 |1
2017-08-06 |2017-08-06 14:01:00.000 |2017-08-06 20:01:00.000 |06:00:00 |05:41:00 |00:19:00 |1
2018-02-05 |2018-02-05 15:20:07.000 |2018-02-05 23:28:33.000 |08:08:26 |07:06:26 |01:02:00 |101
2018-02-06 |2018-02-06 16:03:08.000 |2018-02-07 01:32:08.000 |09:29:00 |08:20:00 |01:09:00 |101
2018-02-07 |2018-02-07 14:57:57.000 |2018-02-07 23:34:36.000 |08:36:39 |07:59:27 |00:37:12 |101
I tried like below :
select
cast(ShitStartTime as date) ShiftDate, ShitStartTime, ShiftEndTime
, concat(right(concat('0', tTime / 60 / 60 % 24), 2), ':', right(concat('0',tTime/ 60 % 60), 2)
,':',right(concat('0',tTime % 60), 2)) Total_Time
,concat(right(concat('0', onF / 60 / 60 % 24), 2), ':', right(concat('0',onF/ 60 % 60), 2)
,':',right(concat('0',onF % 60), 2))OnFloor
,
concat(right(concat('0', offF / 60 / 60 % 24), 2), ':', right(concat('0',offF/ 60 % 60), 2)
,':',right(concat('0',offF % 60), 2))OffFloor, [Emp Id]
from (
select [Emp Id], isnull( min( case when ltrim( rtrim( [Area Of Access]))='in' then Time end ) ,'1900-01-01')ShitStartTime ,
isnull( max( case when ltrim(rtrim([Area Of Access]))='out'then Time end ) ,'1900-01-01')ShiftEndTime,
sum(iif(ltrim(rtrim([Area Of Access]))='in', diff, 0)) onF
,sum(iif(ltrim(rtrim([Area Of Access]))='out', diff, 0)) offF
, sum(diff) tTime
from (
select *, datediff(SECOND, Time, lead(Time) over (partition by [Emp Id], group_ order by Time)) diff
from (
select
*, sum(gr) over (partition by [Emp Id] order by Time rows unbounded preceding) group_
from (
select
*, iif(datediff(hh, lag(Time) over (partition by [Emp Id] order by Time), Time) <= 5, 0, 1) gr
from
empstafflogindetails
) t
) t
) t
group by [Emp Id], group_
) t order by [Emp ID],ShiftDate
But this query is not returning the expected result. Can you please tell me how to write query to achieve this task in SQL Server?
February 26, 2018 at 9:07 am
What is supposed to happen when you have an "IN" and no corresponding "OUT" ? Emp ID 1 has a time in at "2017-08-02 13:00:00.000", then another time in at "2017-08-03 06:30:00.000" before having a time out at "2017-08-03 08:30:00.000". What does that mean in terms of shift?
February 26, 2018 at 9:43 am
This should get you started. It relies on INs and OUTs being properly in sequence. If they're not, you'll need to deal with that first.
WITH InandOut AS (
SELECT
[Emp ID] AS EmpID
, [Area of Access] AS Access
, Time AS EventTime
, LEAD(Time) OVER (PARTITION BY [Emp ID] ORDER BY Time) AS NextTime
FROM empstafflogindetails
)
SELECT
EmpID
, Access
, EventTime
, DATEDIFF(minute,EventTime,NextTime)
FROM InandOut
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply