Besoin d'aide pour un Query SQL

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,

Je viens demander un peu d'aide pour effectuer une recherche via un query dans SQL Server.

Le projet est de faire afficher sur une page web les personnes présentes dans un bâtiment.
Pour cela, je dispose du log du serveur avec toutes les entrées/sorties (badges d'accès IN/OUT).
Dans ce log, j'ai la possibilité d'avoir le numéro du badge, nom, prénom, endroit, heure, entrée ou sortie.

J'arrive déjà via mon query a afficher la dernière personne ayant badgé en entrée et la faire disparaitre dès qu'elle badge en sortie.

J'aimerais faire afficher tout les badges en tenant compte uniquement du dernier événements de chaque personne en entrée.
Est ce que cela est possible?

Voici mon query de départ.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 use emsevents Select TOP 100 * From ( SELECT distinct CardNumber AS Carte, CardHolderFirstName AS Prénom, CardHolderLastName AS Nom, Substring(ZoneEntered, 4, 2) AS Zone from dbo.Events Where EventID = (select MAX (EventID) from events where ZoneEntered is not null And cardNumber is not null And Description like '%Lecteur IN%' And ZoneEntered like '%CH%' And ConditionName like '%GRANTED%' ) ) emsevents
voici ce que cela me donne

Pièce jointe 556557

Je vous remercie d'avance à tous.
Meethyx

Bonjour
En passant ...
Remplacer

Code:

1
2
3
4
5
6
...
Where
EventID = (select MAX (EventID)
from events where ZoneEntered is not null
...

par

Code:

1
2
3
4
5
6
...
Where
EventID in (select MAX (EventID)
from events where ZoneEntered is not null
...

Bonjour,

Si j'ai bien compris, qqch du genre:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select Carte, Prenom, Nom, Zone
from (SELECT CardNumber AS Carte, 
             CardHolderFirstName AS Prenom, 
			 CardHolderLastName AS Nom, 
			 Substring(ZoneEntered, 4, 2) AS Zone,
			 row_number() over (partition by cardnumber order by eventID desc) rn
	  from dbo.Events
      where ZoneEntered is not null
		And cardNumber is not null
		And Description like '%Lecteur IN%'
		And ZoneEntered like '%CH%'
		And ConditionName like '%GRANTED%'
     )
where rn = 1;

15/04/2020, 15h04
MeeThyX

2 pièce(s) jointe(s)

J'ai testé cela mais j'obtiens une erreur .

Pièce jointe 556605

J'ai aussi testé le "in" de vttman mais sans succès.

J'arrive malgré tout comme cela à afficher tout les IN mais je n'arrive pas à prendre le dernier de chaque n° de carte car logiquement le 512 qui apparait est bien un IN mais un OUT est présent au dessus.
Normalement il devrait disparaitre mais ne le fait pas.

Pièce jointe 556609

Un grand merci déjà pour l'aide

Je connais pas SQL Server, mais c'est pas bêtement parce qu'il manque le AS devant rn, et aussi un alias pour le sous-query?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
    select Carte, Prenom, Nom, Zone
    from (SELECT CardNumber AS Carte, 
                 CardHolderFirstName AS Prenom, 
    			 CardHolderLastName AS Nom, 
    			 Substring(ZoneEntered, 4, 2) AS Zone,
    			 row_number() over (partition by cardnumber order by eventID desc)  AS rn
    	  from dbo.Events
          where ZoneEntered is not null
    		And cardNumber is not null
    		And Description like '%Lecteur IN%'
    		And ZoneEntered like '%CH%'
    		And ConditionName like '%GRANTED%'
         ) as S
    where s.rn = 1;

15/04/2020, 15h31
MeeThyX

1 pièce(s) jointe(s)

Alors ça c'est déjà très bien car c'est très propre comme requête mais le 512 est toujours présent.
Il faudrait prendre en compte le dernier évènement de chaque badge seulement.

Pensez-vous que c'est possible?

Voici ce que j'obtiens avec ta requête.

Pièce jointe 556629

Ok dans ce cas il faut filter en utilisant la description (j'ai supposé que OUT était du genre '%Lecteur OUT%', tu adapteras si besoin). Quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  select Carte, Prenom, Nom, Zone
    from (SELECT CardNumber AS Carte, 
                 CardHolderFirstName AS Prenom, 
    			 CardHolderLastName AS Nom, 
    			 Substring(ZoneEntered, 4, 2) AS Zone,
    			 row_number() over (partition by cardnumber order by case when Description like '%Lecteur IN%' then 0 else 1 end, eventID desc)  AS rn,
			 sum(case when Description like '%Lecteur OUT%' then 1 else 0 end) over (partition by cardNumber) AS out_count
    	  from dbo.Events
          where ZoneEntered is not null
    		And cardNumber is not null    		
    		And ZoneEntered like '%CH%'
    		And ConditionName like '%GRANTED%'
         ) as S
    where s.rn = 1 and s.out_cnt = 0;

15/04/2020, 15h53
MeeThyX

Merci je vais tester cela mais peux-tu m'expliquer ce que l'on fait sur le where

Code:

where s.rn = 1 and s.out_cnt = 0;
15/04/2020, 16h03
vanagreg

En gros je compte les lignes par cardnumber où il y a des OUT (que j'appelle out_count). Si j'ai bien compris tu veux qu'il n'y ait pas eu de OUT donc il faut que out_count = 0.
Ensuite, comme tu veux le dernier événement IN, j'utilise la fonction ROW_NUMBER pour assigner un numéro incrémental pour chaque groupe de ligne associé à un cardnumber. Je trie par '%Lecteur IN%' puis par eventid desc pour avoir la dernière ligne IN en premier. Cette ligne aura donc rn = 1.
15/04/2020, 16h40
MeeThyX

1 pièce(s) jointe(s)

Merci pour les explications.
ta requête fonctionne mais apparemment si un out est présent il disparait hors le fichier log que j'ai est repris sur un temps très long et des gens peuvent rentrer et sortir sur la même journée
Voilà un peu le log que j'ai sur le serveur et en fonction de ca, il faut que j'affiche sur une page html les personnes qui sont toujours à l'intérieure.
Si tu regarde le 515 tu verras qu'il est "out" sur le serveur donc logiquement lui seul dois disparaître.

Pièce jointe 556671

En tous cas c'est vraiment gentil de m'aider come tu le fait et je t'en remercie. Je sais que ce n'est pas évident à comprendre.

C'est mieux en effet quand on a un jeu de données pour se donner une idée. C'est encore plus simple en fait, tu veux que le dernier événement soit une entrée, donc:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select Carte, Prenom, Nom, Zone
    from (SELECT CardNumber AS Carte, 
                 CardHolderFirstName AS Prenom, 
    			 CardHolderLastName AS Nom, 
    			 Substring(ZoneEntered, 4, 2) AS Zone,
				 Description As Description,
    			 row_number() over (partition by cardnumber order by eventID desc) AS rn
    	  from dbo.Events
          where ZoneEntered is not null
    		And cardNumber is not null    		
    		And ZoneEntered like '%CH%'
		And Description like '%Lecteur IN%' 
    		And ConditionName like '%GRANTED%'
         ) as S
    where s.rn = 1 ;

15/04/2020, 17h42
MeeThyX

2 pièce(s) jointe(s)

Vraiment désolé mais le dernier ne fonctionne pas pour les supprimer quand c'est sorti.

Voilà le log serveur après passage des badges. tout en IN d'abord et ensuite j'ai sorti uniquement 515, 514 et 513 donc normalement je devrais juste afficher uniquement 405, 511, 512 qui sont logiquement encore à l'intérieure du bâtiment.

Pièce jointe 556708

Voilà le résultat de la requete

Pièce jointe 556712

Je te remercie pour ta patience et pour ton temps également.

Oui c'est moi qui suis étourdi. Il faut que le dernier soit IN, donc:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
  select Carte, Prenom, Nom, Zone
    from (SELECT CardNumber AS Carte, 
                 CardHolderFirstName AS Prenom, 
    			 CardHolderLastName AS Nom, 
    			 Substring(ZoneEntered, 4, 2) AS Zone,
			 Description As description,
    			 row_number() over (partition by cardnumber order by eventID desc) AS rn
    	  from dbo.Events
          where ZoneEntered is not null
    		And cardNumber is not null    		
    		And ZoneEntered like '%CH%'
    		And ConditionName like '%GRANTED%'
         ) as S
    where s.rn = 1 and s.description like '%Lecteur IN%';

15/04/2020, 18h05
MeeThyX

Alors là Super :)

Tu es un champion car ca fonctionne nickel.

Il ne me reste plus qu'a afficher cela dans un html pour l'afficher sur un écran avec un rafraichissement toute les 15 sec. Ca serait déjà bien.

Je vais regarder de mon côté pour mettre cela en place et si besoin je ferais appel à l'aide pour la suite.

Je tiens franchement à te remercier pour ton aide. ;)

Bien à toi,
MeeThyx