Discussion :

[guilld]

Bonjour,

J'ai besoin d'aide sur une requête qui me calculerai le temps (en minutes) entre 2 dates. Datepart(minutes, ..., ...)
Si RES_EVE_ID = 600 => démarrage du chrono
Si RES_EVE_ID = 601 => Arrêt du chrono

Si deux "600" sont à la suite, c'est le plus ancien qui compte.
Si deux "601" sont à la suite, c'est le plus récent qui compte.

La colonne RES_DATA est l'horodate de l'événement.
La colonne RES_DATEEVT est juste le datetime d'enregistrement de l'événement.

C'est sur RES_DATE que le décompte doit être fait..

RES_Data	RES_DateEvt	RES_EVE_ID
2022/2/8 14:18:30	2022/2/8 14:18:39:775	600
2022/2/8 14:33:38	2022/2/8 14:33:54:127	600
2022/2/8 14:33:45	2022/2/8 14:36:7:487	601
2022/2/8 14:36:28	2022/2/8 14:37:0:482	600
2022/2/8 14:37:44	2022/2/8 14:38:4:210	601
2022/2/8 15:23:6	2022/2/8 15:23:24:945	600
2022/2/8 15:24:45	2022/2/8 15:25:26:913	601
2022/2/8 15:26:44	2022/2/8 15:27:10:986	600
2022/2/8 15:27:15	2022/2/8 15:27:57:176	601

Merci bcp pour votre aide..

[Waldar]

J'ai mis votre jeu de test et la requête dans ce fiddle, en rajoutant un événement 601 :
https://dbfiddle.uk/?rdbms=sqlserver...fed84001f9b790

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_grp (res_id, res_Data, res_DateEvt, res_eve_id, grp) as
(
select res_id, res_Data, res_DateEvt, res_eve_id
     , row_number() over(                        order by res_DateEvt asc)
     - row_number() over(partition by res_eve_id order by res_DateEvt asc)
  from RES
)
  ,  cte_data_filter (res_id, res_Data, res_DateEvt, res_eve_id, grp, flt) as
(
select res_id, res_Data, res_DateEvt, res_eve_id, grp
     , case res_eve_id
          when 600 then row_number() over(partition by res_eve_id, grp order by res_DateEvt  asc)
          when 601 then row_number() over(partition by res_eve_id, grp order by res_DateEvt desc)
       end
  from cte_data_grp
)
  ,  cte_data_pair (res_id, res_Data, res_DateEvt, res_eve_id, pair) as
(
select res_id, res_Data, res_DateEvt, res_eve_id
     , (row_number() over(order by res_DateEvt asc) - 1)/ 2
  from cte_data_filter
 where flt = 1
)
  select min(res_Data) as evt_600
       , max(res_Data) as evt_601
       , datediff(second, min(res_Data), max(res_Data)) as duree
    from cte_data_pair
group by pair
order by pair asc;
 
evt_600                      evt_601                      duree
---------------------------  ---------------------------  -----
2022-02-08 14:18:30.0000000  2022-02-08 14:33:45.0000000    915
2022-02-08 14:36:28.0000000  2022-02-08 14:37:44.0000000     76
2022-02-08 15:23:06.0000000  2022-02-08 15:24:45.0000000     99
2022-02-08 15:26:44.0000000  2022-02-08 15:28:15.0000000     91

[guilld]

BIM..!

Médaille d'or de l'efficacité!
Merci pour ton aide !!
Et merci de m'avoir fait connaitre DBFIDDLE!

[sqllm]

Bonjour

Je suis loin d’etre un expert, du coup j’ai trouvé que c’etait un bon exercice

Je pense que cette requête répond au besoin et elle me semble plus simple

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
 
 
WITH tmp AS
  (SELECT 1 AS etat,res_Data,res_eve_id,lag(res_eve_id, 1, 601) OVER (ORDER BY res_Data) AS evt_offset
   FROM RES UNION ALL
   SELECT 2 AS etat,res_Data,res_eve_id,lag(res_eve_id, 1) OVER (ORDER BY res_Data) AS evt_offset
   FROM RES),
     tmp2 AS
  (SELECT etat,res_Data AS debut,lead(res_data) OVER (ORDER BY res_data) AS fin
   FROM tmp
   WHERE ((res_eve_id=600 AND evt_offset=601 AND etat=1)
          OR (res_eve_id=601 AND evt_offset=600 AND etat=2)))
SELECT *, datediff(SECOND, debut, fin)
FROM tmp2
WHERE etat=1

[JeitEmgie]

Bonjour

Je suis loin d’etre un expert, du coup j’ai trouvé que c’etait un bon exercice

Je pense que cette requête répond au besoin et elle me semble plus simple

Plus simple à lire ne veut pas dire plus efficace : vous faites un double FULL SCAN de vos données...
tant qu'on joue avec 10 rows... essayez avec 10 millions...

[Waldar]

Vous êtes sévère, le second full scan se fera depuis le cache, et puis j'aime bien sa proposition.
Il manque la prise en compte du dernier code 601, mais sinon c'est bien.

[JeitEmgie]

Vous êtes sévère, le second full scan se fera depuis le cache, et puis j'aime bien sa proposition.
Il manque la prise en compte du dernier code 601, mais sinon c'est bien.

Oui l'idée de départ n'est pas mal du tout, pour l'améliorer :
ne pas faire UNION ALL dans tmp :
pas besoin de "etat" à ce niveau, et calculer les 2 offset dans 1 seul SELECT en tant que evt_offset1 et evt_offset2
et dans tmp2 :
remplacer "etat" par evt_offset1, et dans la WHERE remplacer les 2 evt_offset par evt_offset1 et evt_offset2 resp.
et finalement le WHERE du SELECT final : evt_offset = 601

et restera le problème du dernier 601

[sqllm]

Bonjour,

Comme dit je suis pas expert

mais voici la requête améliorée en fonction de vos remarques. Effectivement c'est mieux !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH tmp AS (SELECT res_Data,res_eve_id,
             lead(res_eve_id, 1,600) OVER (ORDER BY res_Data) AS evt_debut,
             lag(res_eve_id, 1, 601) OVER (ORDER BY res_Data) AS evt_fin
   FROM RES)
   ,tmp2 as (select res_eve_id, res_Data as debut,
            lead(res_Data) OVER (ORDER BY res_Data) as fin
   from tmp where ((res_eve_id=600 and evt_fin = 601)
                    Or (res_eve_id=601 and evt_debut = 600)))
 
   select debut, fin, datediff(SECOND, debut, fin) from tmp2
   where res_eve_id = 600 order by debut