emp logindetails based on conditions in sql server
-
I have a question about SQL Server. Please tell me how to solve login and logout time calculation in SQL Server based on conditions.
If the same empid works multiple shifts, multiple shifts calculation for same date must be time difference is 5 hours then that date consider as multiple shifts for that emp
OnFloor time how much time they spent
OffFloor time how much time they spent
Sample input data :CREATE TABLE [dbo].[emp](
[Emp Id] [float] NULL,
[Area Of Access] [nvarchar](255) NULL,
[Time] [datetime] NULL,
[floor] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'IN', CAST(N'2018-03-03T03:36:58.000' AS DateTime), N'Common Area')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'OUT', CAST(N'2018-03-03T03:38:55.000' AS DateTime), N'Common Area')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'OUT', CAST(N'2018-03-05T18:54:00.000' AS DateTime), N'Common Area')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'IN', CAST(N'2018-03-05T18:54:13.000' AS DateTime), N'Common Area')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'IN', CAST(N'2018-03-05T18:54:32.000' AS DateTime), N'Common Area')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'IN', CAST(N'2018-03-05T18:55:01.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (10, N'OUT', CAST(N'2018-03-05T19:58:41.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'IN', CAST(N'2018-02-06T16:03:08.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'OUT', CAST(N'2018-02-06T22:01:40.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'IN', CAST(N'2018-02-06T22:42:15.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'OUT', CAST(N'2018-02-07T00:33:19.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'IN', CAST(N'2018-02-07T10:13:11.000' AS DateTime), N'Production Floor')
GO
INSERT [dbo].[emp] ([Emp Id], [Area Of Access], [Time], [floor]) VALUES (20, N'OUT', CAST(N'2018-02-07T15:13:10.000' AS DateTime), N'Production Floor')
GO
Based on above data I want output like below :
I tried like below :
select
ShiftDate, ShitStartTime, ShiftEndTime
, Total_Time = right(concat('0', Total_Time / 3600), 2) + ':' + right(concat('0', Total_Time % 3600 / 60), 2) + ':' + right(concat('0', Total_Time % 60), 2)
, OnFloor = right(concat('0', OnFloor / 3600), 2) + ':' + right(concat('0', OnFloor % 3600 / 60), 2) + ':' + right(concat('0', OnFloor % 60), 2)
, OffFloor = right(concat('0', OffFloor / 3600), 2) + ':' + right(concat('0', OffFloor % 3600 / 60), 2) + ':' + right(concat('0', OffFloor % 60), 2)
, [Emp ID]
from (
select
[Emp ID], ShiftDate = cast(min(Time) as date)
, ShitStartTime = isnull( min( case when ltrim(rtrim([Area Of Access]))='in'
then
Time end ) ,'1900-01-01')
, ShiftEndTime = isnull( max( case when ltrim(rtrim([Area Of Access]))='out'
then
Time end ) ,'1900-01-01')
, Total_Time = sum(ss)
, OnFloor = sum(iif(ltrim(rtrim([Area Of Access]))='in', ss, 0)), OffFloor = sum(iif(ltrim(rtrim([Area Of Access]))='out', ss, 0))
from (
select
*, ss = datediff(ss, Time, lead(Time) over (partition by [Emp ID], grp order by Time))
from (
select
*, grp = sum(diff) over (partition by [Emp ID] order by Time)
from (
select
*, diff = iif(datediff(mi, lag(Time) over (partition by [Emp ID] order by Time), Time) > 300and [Area Of Access]='in'
, 1, 0)
from
emp
) t
) t
) t
group by [Emp ID], grp
) t
above query is not given expected result . Please tell me how to wirte query to achieve this task in SQL Server -
Can you post the expected results please?
😎In the meantime, this might help
USE TEEST;
GO
SET NOCOUNT ON;IF OBJECT_ID(N'dbo.TBL_EMP022') IS NOT NULL DROP TABLE dbo.TBL_EMP022;
CREATE TABLE dbo.TBL_EMP022(
[Emp Id] [float] NULL,
[Area Of Access] [nvarchar](255) NULL,
[Time] [datetime] NULL,
[floor] [nvarchar](255) NULL
);
INSERT dbo.TBL_EMP022 ([Emp Id], [Area Of Access], [Time], [floor])
VALUES
(10, N'IN', CAST(N'2018-03-03T03:36:58.000' AS DateTime), N'Common Area')
,(10, N'OUT', CAST(N'2018-03-03T03:38:55.000' AS DateTime), N'Common Area')
,(10, N'OUT', CAST(N'2018-03-05T18:54:00.000' AS DateTime), N'Common Area')
,(10, N'IN', CAST(N'2018-03-05T18:54:13.000' AS DateTime), N'Common Area')
,(10, N'IN', CAST(N'2018-03-05T18:54:32.000' AS DateTime), N'Common Area')
,(10, N'IN', CAST(N'2018-03-05T18:55:01.000' AS DateTime), N'Production Floor')
,(10, N'OUT', CAST(N'2018-03-05T19:58:41.000' AS DateTime), N'Production Floor')
,(20, N'IN', CAST(N'2018-02-06T16:03:08.000' AS DateTime), N'Production Floor')
,(20, N'OUT', CAST(N'2018-02-06T22:01:40.000' AS DateTime), N'Production Floor')
,(20, N'IN', CAST(N'2018-02-06T22:42:15.000' AS DateTime), N'Production Floor')
,(20, N'OUT', CAST(N'2018-02-07T00:33:19.000' AS DateTime), N'Production Floor')
,(20, N'IN', CAST(N'2018-02-07T10:13:11.000' AS DateTime), N'Production Floor')
,(20, N'OUT', CAST(N'2018-02-07T15:13:10.000' AS DateTime), N'Production Floor');SELECT
EM.[Emp Id]
,EM.[Area Of Access]
,EM.[Time]
,ISNULL((DATEDIFF(SECOND
,LAG(EM.[Time],1,NULL) OVER
(
PARTITION BY EM.[Emp Id]
ORDER BY EM.[Time] ASC
)
,EM.[Time]
) / 3600.0),0) AS HOUR_SPENT
,EM.[floor]
FROM dbo.TBL_EMP022 EM
ORDER BY EM.[Emp Id] ASC
,EM.Time ASC; - Eirikur Eiriksson - Saturday, April 7, 2018 4:29 AM
expected out is not getting using above script: expected output is :
ShiftDate |ShitStartTime |ShiftEndTime |Total_Time |OnFloor |OffFloor |Emp ID
2018-03-03 |2018-03-03 03:36:58.000 |2018-03-03 03:38:55.000 |00:01:57 |00:01:57 |00:00:00 |10
2018-03-05 |2018-03-05 18:54:13.000 |2018-03-05 19:58:41.000 |01:04:41 |01:04:28 |00:00:13 |10
2018-02-06 |2018-02-06 16:03:08.000 |2018-02-07 00:33:19.000 |08:30:11 |07:49:36 |00:40:35 |20
2018-02-07 |2018-02-07 10:13:11.000 |2018-02-07 15:13:10.000 |04:59:59 |04:59:59 |00:00:00 |20 - asranantha - Saturday, April 7, 2018 4:57 AM
If the shift time is '2018-02-07 00:33:19.000 ' which is greater than '2018-02-07 00:00:00.000 ' then it should be considered as '2018-02-07' not '2018-02-06' right?
(20, N'IN', CAST(N'2018-02-06T22:42:15.000' AS DateTime), N'Production Floor')
,(20, N'OUT', CAST(N'2018-02-07T00:33:19.000' AS DateTime), N'Production Floor')Saravanan
- saravanatn - Saturday, April 7, 2018 5:30 AM
same day may work multiple shifts for any employee .here time difference of out and in time more than 5 hours then consider next shift.
here time difference of in and out time more or less than 5 hours then consider same shift(one shift ) only. -
Whether or not someone works multiple shifts isn't the biggest problem, but having data that represents multiple consecutive "clock punches" of the same type (either IN or OUT), seems illogical. How can you punch in 3 times in a row? Or punch out twice in a row? This is what your data suggests, and the illogic of that needs to be clearly explained. So far, it has not, and thus it's difficult, if not impractical, to determine the solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply