Discussion :

[Sniper69]

Salut à tous,

J'aurais besoin de votre assistance, s'il vous plaît. Je suis en train de travailler sur une requête de sélection à partir d'une table de pointage des collaborateurs (je travail dans un environnement MS ACCESS 2016), mais le problème est que cette table contient des doublons avec de légères variations dans les horaires.

Un exemple pourrait rendre les choses plus claires.

Table sources contient les données suivants :

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

Résultats souhaités :

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


à l'aide de ChatGPT j'ai obtenu cette requête qui fonctionne presque sauf que ça me donne pour chaque groupe de login/logout la 2ème connexions ey n'ont pas la première.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
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

Pouvez-vous m'aider pls ?

merci d'avance.

[escartefigue]

La difficulté ici est de définir pourquoi vous sélectionnez la borne de début de la première ligne avec la borne de fin de la deuxième, plutôt que la borne de fin de la troisième ou de la quatrième...
On comprend intuitivement que quand l'écart est de l'ordre de quelques secondes, on prend la ligne la plus forte, mais en SQL, il faut le traduire par une règle. Quelle est-elle ?

[Sniper69]

La difficulté ici est de définir pourquoi vous sélectionnez la borne de début de la première ligne avec la borne de fin de la deuxième, plutôt que la borne de fin de la troisième ou de la quatrième...
On comprend intuitivement que quand l'écart est de l'ordre de quelques secondes, on prend la ligne la plus forte, mais en SQL, il faut le traduire par une règle. Quelle est-elle ?

L'objectif est de créer une timeline de connexion/déconnexion, d'une façon plus concrète conserver que la première login et la dernière logout de chaque intervalle de connexion/déconnexion

un autre exemple pour simplifier le besoin :

1er tableau = data source
2ème tableau = le résultat souhaité



j'espère que c'est clair maintenant

[escartefigue]

C'est clair depuis le départ, mais il faut une règle traduisible en SQL
Là, dans l'exemple, les lignes en jaune sont en doublon ok, mais la couleur jaune ce n'est pas une règle traduisible en SQL
Par exemple, la règle peut être : il considérer qu'il y a doublon quand l'écart est de moins d'une heure, moins de 10 minutes...

[Sniper69]

Je m'excuse, mais je n'ai pas saisi pleinement ta question. Pour moi, la règle consiste à conserver le (min LoginTime) et le (max LogoutTime) de chaque intervalle chevauché.

Le script que j'ai initialement intégré fonctionne partiellement. Il conserve les intervalles souhaités, mais inclut la dernière connexion plutôt que la première.

Désolé, je suis encore novice dans le langage SQL.

[escartefigue]

C'est ce que j'ai mis en rouge ci-dessous qui manquait comme information

Je m'excuse, mais je n'ai pas saisi pleinement ta question. Pour moi, la règle consiste à conserver le (min LoginTime) et le (max LogoutTime) de chaque intervalle chevauché.

La méthode classique pour satisfaire ce genre de besoins est d'utiliser la méthode dite "Tabibitosan", c'est l'objet de la réponse de Waldar qui précède, il faut pour cela que votre SGBD accepte les fonctions fenêtrés (c'est à dire que ce ne soit pas Access, ou MySQL dans une version antérieure à la V8).

[Waldar]

Si votre database n'a pas de fonctions natives pour gérer ceci, la requête SQL peut être complexe et pas très performantes à l'exécution.
Essayez ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
with cte_data (Employee_Id, LoginTime, LogoutTime) as
(
select 8858043, timestamp '2024-01-06 07:58:48', timestamp '2024-01-06 14:03:05' union all
select 8858043, timestamp '2024-01-06 07:59:47', timestamp '2024-01-06 14:05:05' union all
select 8858043, timestamp '2024-01-06 15:00:06', timestamp '2024-01-06 18:05:40' union all
select 8858043, timestamp '2024-01-06 15:00:29', timestamp '2024-01-06 18:01:40'
)
  ,  cte_LogoutMax (Employee_Id, LoginTime, LogoutTime, LogoutTime_max) as
(
select Employee_Id, LoginTime, LogoutTime
     , max(LogoutTime) over(partition by Employee_Id order by LoginTime, LogoutTime rows unbounded preceding) as LogoutTime_max
  from cte_data
)
  ,  cte_GrpStart (Employee_Id, LoginTime, LogoutTime, GrpStart) as
(
select Employee_Id, LoginTime, LogoutTime
     , case when LoginTime <= lag(LogoutTime_max) over(partition by Employee_Id order by LoginTime, LogoutTime) then 0 else 1 end
  from cte_LogoutMax
)
  ,  cte_GrpId (Employee_Id, LoginTime, LogoutTime, GrpId) as
(
select Employee_Id, LoginTime, LogoutTime
     , sum(GrpStart) over(partition by Employee_Id order by LoginTime rows unbounded preceding)
  from cte_GrpStart
)
  select Employee_Id
       , min(LoginTime)  as LoginTime
       , max(LogoutTime) as LogoutTime
    from cte_GrpId
group by Employee_Id, GrpId
order by Employee_Id, GrpId;
 
Employee_Id  LoginTime            LogoutTime         
-----------  -------------------  -------------------
    8858043  2024-01-06 07:58:48  2024-01-06 14:05:05
    8858043  2024-01-06 15:00:06  2024-01-06 18:05:40

Edit: j'ai corrigé une typo dans le code.

[vanagreg]

Bonjour,

Dans Oracle avec le Pattern Matching:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select * from Telephonie 
match_recognize(partition by employee_id
                order by logintime
                measures min(logintime) as logintime,
                         greatest(max(a.logouttime), next(a.logouttime)) as logouttime
                pattern (a+)
                define A as a.logouttime > next(a.logintime)
               );