Help pls SQL Query

1

Hello everyone,

I would need your assistance, please. I am working on a select query from an employee attendance table (I work in an MS ACCESS 2016 environment), but the problem is that this table contains duplicates with slight variations in the timings.

An example might make things clearer.

The source table contains the following data:

EMPLOYEE_ID | LoginTime | LogoutTime
8858043_IPSATMO | 2024-01-06 07:58:48.000 | 2024-01-06 14:03:05.000
8858043_IPSATMO | 2024-01-06 07:59:47.000 | 2024-01-06 14:05:05.000
8858043_IPSATMO | 2024-01-06 15:00:06.000 | 2024-01-06 18:05:40.000
8858043_IPSATMO | 2024-01-06 15:00:29.000 | 2024-01-06 18:01:40.000

Desired results:

EMPLOYEE_ID | LoginTime | LogoutTime
8858043_IPSATMO | 2024-01-06 07:58:48.000 | 2024-01-06 14:05:05.000
8858043_IPSATMO | 2024-01-06 15:00:06.000 | 2024-01-06 18:05:40.000

Using ChatGPT, I obtained the following query that almost works, except it gives me the second connections for each login/logout group and not the first.

Code:

sql
Copy code
SELECT
CD1.EMPLOYEE_ID,
MIN(CD1.LoginTime) AS FirstLogin,
MAX(CD2.LogoutTime) AS LastLogout
FROM
Telephonie AS CD1
LEFT JOIN
Telephonie AS CD2
ON
(CD1.LoginTime < CD2.LogoutTime) AND (CD1.EMPLOYEE_ID = CD2.EMPLOYEE_ID)
WHERE
NOT EXISTS (
SELECT 1
FROM Telephonie AS CD3
WHERE CD3.EMPLOYEE_ID = CD1.EMPLOYEE_ID
AND CD3.LoginTime > CD1.LoginTime
AND CD3.LoginTime < CD2.LogoutTime
)
GROUP BY
CD1.EMPLOYEE_ID, CD1.LoginTime
Can you please help me?

Thank you in advance.

2

SQLTeam is a Microsoft SQL Server site so you may get better replies on a MS Access site.

I think the main thing you need to address is why you are getting duplicates in the first place.

With regards to the question, we would normally expect consumable test data. eg

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
	Employee_Id char(15) NOT NULL
	,LoginTime datetime NOT NULL
	,LogoutTime datetime NOT NULL
	,PRIMARY KEY (Employee_Id, LoginTime)
);
INSERT INTO #t
VALUES ('8858043_IPSATMO', '20240106 07:58:48', '20240106 14:03:05')
	,('8858043_IPSATMO', '20240106 07:59:47', '20240106 14:05:05')
	,('8858043_IPSATMO', '20240106 15:00:06', '20240106 18:05:40')
	,('8858043_IPSATMO', '20240106 15:00:29', '20240106 18:01:40');
GO

In SQL Server there are a number of ways this can be done using Windowed functions. eg

WITH PrevTimes
AS
(
	SELECT Employee_Id, LoginTime, LogoutTime
		,LAG(LoginTime) OVER (PARTITION BY Employee_Id ORDER BY LoginTime) AS PrevTime
	FROM #t
)
,GroupTimes
AS
(
	SELECT Employee_Id, LoginTime, LogoutTime
		,IIF(DATEDIFF(minute, PrevTime, LoginTime) <= 3, PrevTime, LoginTime)  AS GroupTime
	FROM PrevTimes
)
SELECT Employee_Id
	,MIN(LoginTime) AS LoginTime
	,MAX(LogoutTime) AS LogoutTime
FROM GroupTimes
GROUP BY Employee_Id, GroupTime;

As I do not think MS Access contains either Windowed functions or CTEs, this could be re-written as:

SELECT D.Employee_Id
	,MIN(D.LoginTime) AS LoginTime
	,MAX(T3.LogoutTime) AS LogoutTime
FROM
(
	SELECT T1.Employee_Id, T1.LoginTime
		,IIF(DATEDIFF(minute, MAX(T2.LoginTime), T1.LoginTime) <= 3, MAX(T2.LoginTime), T1.LoginTime)  AS GroupTime
	FROM #t T1
		LEFT JOIN #t T2
			ON T1.Employee_Id = T2.Employee_Id
				AND T1.LoginTime > T2.LoginTime
	GROUP BY T1.Employee_Id, T1.LoginTime
) D
	JOIN #t T3
		ON D.Employee_Id = T3.Employee_Id
			AND D.LoginTime = T3.LoginTime
GROUP BY D.Employee_Id, D.GroupTime;

Which might translate into MS Access as:

SELECT D.Employee_Id
	,MIN(D.LoginTime) AS LoginTime
	,MAX(T3.LogoutTime) AS LogoutTime
FROM
(
	SELECT T1.Employee_Id, T1.LoginTime
		,IIF(DATEDIFF(n, MAX(T2.LoginTime), T1.LoginTime) <= 3, MAX(T2.LoginTime), T1.LoginTime)  AS GroupTime
	FROM Telephonie AS T1
		LEFT JOIN Telephonie AS T2
			ON T1.Employee_Id = T2.Employee_Id
				AND T1.LoginTime > T2.LoginTime
	GROUP BY T1.Employee_Id, T1.LoginTime
) AS D
	JOIN Telephonie AS T3
		ON D.Employee_Id = T3.Employee_Id
			AND D.LoginTime = T3.LoginTime
GROUP BY D.Employee_Id, D.GroupTime;
3

To emphasize what @Ifor posted...

This is the most important thing on this entire thread. Something is broken on the original time keeping and it needs to be fixed instead of just patching overlaps. It could be software, hardware, or a combo of the two. It's going to bite you in the future because you're not dealing with the actual problem. Have them fix the real problem here.

4

hi

hope this helps

I am assuming that the login time is grouped by the hour
other wise
something else that groups the records for the result set you want

create data script

DROP TABLE #t

CREATE TABLE #t
(
Employee_Id char(15) NOT NULL
,LoginTime datetime NOT NULL
,LogoutTime datetime NOT NULL
,PRIMARY KEY (Employee_Id, LoginTime)
);
INSERT INTO #t
VALUES ('8858043_IPSATMO', '20240106 07:58:48', '20240106 14:03:05')
,('8858043_IPSATMO', '20240106 07:59:47', '20240106 14:05:05')
,('8858043_IPSATMO', '20240106 15:00:06', '20240106 18:05:40')
,('8858043_IPSATMO', '20240106 15:00:29', '20240106 18:01:40');
GO

SELECT  
   Employee_Id
   ,min(logintime)
   ,max(LogoutTime)
FROM 
  #t 
GROUP BY 
   Employee_Id
  ,convert(varchar(10), LoginTime, 112)+datepart(hh,LoginTime)

image
image1524×529 44.4 KB