Discussion :

[MeeThyX]

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 :

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

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



Je vous remercie d'avance à tous.
Meethyx

[vttman]

Bonjour
En passant ...
Remplacer

Code :

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

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

par

Code :

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

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

[vanagreg]

Bonjour,

Si j'ai bien compris, qqch du genre:

Code :

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

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;

[MeeThyX]

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



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.



Un grand merci déjà pour l'aide

[vanagreg]

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 :

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

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;

[MeeThyX]

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.

[vanagreg]

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 :

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

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;

[MeeThyX]

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

Code :

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

where s.rn = 1 and s.out_cnt = 0;

[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.

[MeeThyX]

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.



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.

[vanagreg]

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 :

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

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 ;

[MeeThyX]

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.



Voilà le résultat de la requete



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

[vanagreg]

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

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
 
  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%';

[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