Partage

Limiter à une ligne par ID

SQL Server

ValterJeronimo1

17 février 2021 à 10:18:17

Bonjour, j'utilise cette requête pour afficher tous les ID avec une qui correspondent à une alerte. Ce qui fonctionne.

Par contre j'aimerais limiter à une seule ligne par ID. Hors avec ma requête tout ID avec plusieurs alertes s'affiche.

Comment faire une limit à 1 pour chaque ID ?

J'ai tenté LIMIT et DISTINCT sans résultat. Une idée ?

L'ID à prendre en compte est ccEvents.ccCallcardUid.

SELECT ccEvents.ccCallcardUid, ccEvents.EventText, ccEvents.CreationUtc, FORMAT (ccEvents.CreationUtc, 'dd.MM.yyyy - HH:mm:ss') AS utc, 

ccCardStamps.Complement AS Complement, ccCardStamps.ccStampTypeConst AS code_alarme, ccCardStamps.ccStatusConst AS code_alarme_carte, 
FORMAT(DATEADD(hour,1,ccCardStamps.CreationUtc), 'HH:mm:ss') AS heure_alarme,

ccLocations.geoSectorId AS secteur, ccLocations.StreetNumber AS numero, ccLocations.PlaceName AS rue, ccLocations.CityName AS commune,

ccCallcards.ccArchiveTypeConst, ccCallcards.Identifier, 

ccPhoneCalls.ccCallcardUid, ccPhoneCalls.ccPhoneCallUid, ccPhoneCallStamps.ccPhoneCallUid, ccPhoneCallStamps.ccPhoneCallStateConst, FORMAT (ccPhoneCallStamps.CreationUtc, 'HH:mm:ss' ),

ccPhoneCallStamps.ccPhoneCallUid, ccPhoneCallStamps.ccPhoneCallStateConst, ccPhoneCallStamps.CreationUtc, 
FORMAT(DATEADD(hour,1,ccPhoneCallStamps.CreationUtc), 'HH:mm:ss') AS heure_prise_appel,

ccArchiveTypes.ccArchiveTypeConst, ccArchiveTypes.Name AS type_archive

FROM  ccEvents

INNER JOIN  ccCardStamps
ON ccEvents.ccCallcardUid = ccCardStamps.ccCallcardUid

INNER JOIN  ccLocations
ON ccEvents.ccCallcardUid = ccLocations.ccCallcardUid

INNER JOIN ccCallcards
ON ccEvents.ccCallcardUid = ccCallcards.ccCallcardUid

INNER JOIN ccArchiveTypes
ON ccCallcards.ccArchiveTypeConst = ccArchiveTypes.ccArchiveTypeConst


INNER JOIN ccPhoneCalls
ON ccEvents.ccCallcardUid = ccPhoneCalls.ccCallcardUid

INNER JOIN ccPhoneCallStamps
ON ccPhoneCalls.ccPhoneCallUid = ccPhoneCallStamps.ccPhoneCallUid

WHERE ccEvents.CreationUtc BETWEEN @DateFrom AND DATEADD(hour,24,@DateTo)

AND ccCardStamps.Complement = 'Véhicule non engagé'

AND ccArchiveTypes.Name = 'Normal'

AND ccPhoneCallStamps.ccPhoneCallStateConst = '1'

ORDER BY ccEvents.CreationUtc DESC;

-
Edité par ValterJeronimo1 17 février 2021 à 10:21:06

Benzouye

17 février 2021 à 11:06:28

Bonjour,

ValterJeronimo1 a écrit:

avec ma requête tout ID avec plusieurs alertes s'affiche [...] L'ID à prendre en compte est ccEvents.ccCallcardUid

Pour avancer, il faudrait nous préciser :

de quelle table ccCallcardUid est-il la clé primaire ?
quelles sont les cardinalités entre les tables en jeu ? 1 callcard = plusieurs events ? 1 phoneCall = plusieurs phoneCallStamps ?

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

17 février 2021 à 12:01:21

Bonjour Benzouye

de quelle table ccCallcardUid est-il la clé primaire ? De ccEvents

quelles sont les cardinalités entre les tables en jeu ? 1 callcard = plusieurs events ? Non une seul event
1 phoneCall = plusieurs phoneCallStamps ? dans chaque Callcard il y a plusieurs phoneCallStamps

Si je fais ceci pour afficher uniquement les ID distincts ça fonctionne:

SELECT DISTINCT ccEvents.ccCallcardUid AS id

FROM ccEvents

INNER JOIN ccCardStamps
ON ccEvents.ccCallcardUid = ccCardStamps.ccCallcardUid

INNER JOIN ccPhoneCalls
ON ccEvents.ccCallcardUid = ccPhoneCalls.ccCallcardUid

INNER JOIN ccPhoneCallStamps
ON ccPhoneCalls.ccPhoneCallUid = ccPhoneCallStamps.ccPhoneCallUid

INNER JOIN ccCallcards
ON ccEvents.ccCallcardUid = ccCallcards.ccCallcardUid

INNER JOIN ccArchiveTypes
ON ccCallcards.ccArchiveTypeConst = ccArchiveTypes.ccArchiveTypeConst

WHERE ccEvents.CreationUtc BETWEEN @DateFrom AND DATEADD(hour,24,@DateTo)

AND (ccCardStamps.Complement = 'Véhicule non engagé'
AND ccPhoneCallStamps.ccPhoneCallStateConst = '1'
AND ccArchiveTypes.Name = 'Normal')

-
Edité par ValterJeronimo1 17 février 2021 à 12:06:39

Benzouye

17 février 2021 à 12:27:37

Je n'arrive pas à saisir où les lignes se multiplient ...

Quelle est la table pour laquelle un event apparaît plusieurs fois ?

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

17 février 2021 à 12:42:30

C'est sur la table ccEvents.

En fait ce que j'aimerais c'est que s'il y a plusieurs lignes pour un ccEvents.ccCallcardUid, il n'y ai que la première qui soit prise.

Voici comment je fais afficher ma requete dans report Builder:

Ce qui se passe c'est que s'il y a plusieurs heure_alarme (ccCardStamps), ça me crée plusieurs lignes, ou s'il y a plusieurs heure_prise_appel (ccPhoneCallStamps) ça me crée autant de lignes aussi.

Benzouye

17 février 2021 à 12:50:15

Donc il faut décider quelle information affichée ...

Si je reprends ta requête :

SELECT
	E.ccCallcardUid,
	E.EventText,
	E.CreationUtc,
	FORMAT( E.CreationUtc, 'dd.MM.yyyy - HH:mm:ss' ) AS utc,
	CS.Complement AS Complement,
	CS.ccStampTypeConst AS code_alarme,
	CS.ccStatusConst AS code_alarme_carte,
	FORMAT( DATEADD( hour, 1, CS.CreationUtc), 'HH:mm:ss') AS heure_alarme,
	L.geoSectorId AS secteur,
	L.StreetNumber AS numero,
	L.PlaceName AS rue,
	L.CityName AS commune,
	CC.ccArchiveTypeConst,
	CC.Identifier,
	CC.ccCallcardUid,
	CC.ccPhoneCallUid,
	PCS.ccPhoneCallUid,
	PCS.ccPhoneCallStateConst,
	FORMAT( PCS.CreationUtc, 'HH:mm:ss' ),
	PCS.ccPhoneCallUid,
	PCS.ccPhoneCallStateConst,
	PCS.CreationUtc,
	FORMAT( DATEADD( hour, 1, PCS.CreationUtc ), 'HH:mm:ss' ) AS heure_prise_appel,
	TA.ccArchiveTypeConst,
	TA.Name AS type_archive
FROM
	ccEvents E
		INNER JOIN ccCardStamps CS
			ON E.ccCallcardUid = CS.ccCallcardUid
		INNER JOIN ccLocations L
			ON E.ccCallcardUid = L.ccCallcardUid
		INNER JOIN ccCallcards CC
			ON E.ccCallcardUid = CC.ccCallcardUid
		INNER JOIN ccArchiveTypes TA
			ON CC.ccArchiveTypeConst = TA.ccArchiveTypeConst
		INNER JOIN ccPhoneCalls PC
			ON E.ccCallcardUid = PC.ccCallcardUid
		INNER JOIN ccPhoneCallStamps PCS
			ON PC.ccPhoneCallUid = PCS.ccPhoneCallUid
WHERE
	E.CreationUtc BETWEEN @DateFrom AND DATEADD( hour, 24, @DateTo )
	AND CS.Complement = 'Véhicule non engagé'
	AND TA.Name = 'Normal'
	AND PCS.ccPhoneCallStateConst = '1'
ORDER BY E.CreationUtc DESC;

ValterJeronimo1 a écrit:

il y a plusieurs heure_alarme (ccCardStamps), [...] il y a plusieurs heure_prise_appel (ccPhoneCallStamps)

Ligne 6, 7 et 8, tu affiches des informations de la table ccCardStamps (alertes).

Si il y en plusieurs mais que tu ne dois en avoir plus qu'une d'affichée, laquelle voudrais-tu conserver ?

Ligne 18 à 24, tu affiches des informations de la table ccPhoneCallStamps (prises d'appel).

Si il y en plusieurs mais que tu ne dois en avoir plus qu'une d'affichée, laquelle voudrais-tu conserver ?

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

17 février 2021 à 13:00:28

J'aimerais prendre la plus ancienne au niveau du de la date et heure.

je garderai pour le coup les lignes

6 et 23/24

-
Edité par ValterJeronimo1 17 février 2021 à 13:02:43

Benzouye

17 février 2021 à 13:11:10

Il faut donc faire des sous-requêtes pour rechercher l'alerte la plus ancienne et la prise d'appel la plus ancienne.

SELECT
	E.ccCallcardUid,
	E.EventText,
	E.CreationUtc,
	FORMAT( E.CreationUtc, 'dd.MM.yyyy - HH:mm:ss' ) AS utc,
	CS.Complement AS Complement,
	CS.ccStampTypeConst AS code_alarme,
	CS.ccStatusConst AS code_alarme_carte,
	FORMAT( DATEADD( hour, 1, CS.CreationUtc), 'HH:mm:ss') AS heure_alarme,
	L.geoSectorId AS secteur,
	L.StreetNumber AS numero,
	L.PlaceName AS rue,
	L.CityName AS commune,
	CC.ccArchiveTypeConst,
	CC.Identifier,
	CC.ccCallcardUid,
	CC.ccPhoneCallUid,
	PCS.ccPhoneCallUid,
	PCS.ccPhoneCallStateConst,
	FORMAT( PCS.CreationUtc, 'HH:mm:ss' ),
	PCS.ccPhoneCallUid,
	PCS.ccPhoneCallStateConst,
	PCS.CreationUtc,
	FORMAT( DATEADD( hour, 1, PCS.CreationUtc ), 'HH:mm:ss' ) AS heure_prise_appel,
	TA.ccArchiveTypeConst,
	TA.Name AS type_archive
FROM
	ccEvents E
			
		INNER JOIN ccLocations L
			ON E.ccCallcardUid = L.ccCallcardUid
		INNER JOIN ccCallcards CC
			ON E.ccCallcardUid = CC.ccCallcardUid
		INNER JOIN ccArchiveTypes TA
			ON CC.ccArchiveTypeConst = TA.ccArchiveTypeConst
		INNER JOIN ccPhoneCalls PC
			ON E.ccCallcardUid = PC.ccCallcardUid
	
		INNER JOIN (
				-- CallCard la plus ancienne
				SELECT
					ccCallcardUid
					MIN( CreationUtc ) AS CreationUtc
				FROM ccCardStamps
				WHERE Complement = 'Véhicule non engagé'
				GROUP BY ccCallcardUid
			) MCS
			ON E.ccCallcardUid = MCS.ccCallcardUid
		INNER JOIN ccCardStamps CS
			ON MCS.ccCallcardUid = CS.ccCallcardUid
			AND MCS.CreationUtc = CS.CreationUtc
			
		INNER JOIN (
				-- PhoneCallStamps la plus ancienne
				SELECT
					ccPhoneCallUid
					MIN( CreationUtc ) AS CreationUtc
				FROM ccPhoneCallStamps
				WHERE ccPhoneCallStateConst = '1'
				GROUP BY ccCallcardUid
			) MPCS
			ON PC.ccPhoneCallUid = MPCS.ccPhoneCallUid
		INNER JOIN ccPhoneCallStamps PCS
			ON MPCS.ccPhoneCallUid = PCS.ccPhoneCallUid
			AND MPCS.CreationUtc = PCS.CreationUtc
WHERE
	E.CreationUtc BETWEEN @DateFrom AND DATEADD( hour, 24, @DateTo )
	AND TA.Name = 'Normal'
ORDER BY E.CreationUtc DESC;

-
Edité par Benzouye 17 février 2021 à 13:11:43

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

17 février 2021 à 13:18:09

OK merci.

Je vois que tu as ajouté des initiales comme E a ccCallcardUid.

C'est une manière de mieux identifier les champs ?

Benzouye

17 février 2021 à 13:43:30

Ce sont des alias (noms de remplacement) pour les tables. Ils sont déclarés dans la clause FROM après les noms des tables, et cela permet de simplifier l'écriture des requêtes, on gagne en lisibilité

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

19 février 2021 à 13:53:41

Hello Benzouye,

J'ai testé ta solution. Il y avait apparemment des petites erreurs de syntaxe selon SQL Manager.

Voici le code que j'ai mis :

DECLARE @DateFrom datetime, @DateTo datetime
SET @DateFrom = '2020-02-03'
SET @DateTo = '2021-02-18'
SELECT
    E.ccCallcardUid,
    E.EventText,
    E.CreationUtc,
    FORMAT( E.CreationUtc, 'dd.MM.yyyy - HH:mm:ss' ) AS utc,
    CS.Complement AS Complement,
    CS.ccStampTypeConst AS code_alarme,
    CS.ccStatusConst AS code_alarme_carte,
    FORMAT( DATEADD( hour, 1, CS.CreationUtc), 'HH:mm:ss') AS heure_alarme,
    L.geoSectorId AS secteur,
    L.StreetNumber AS numero,
    L.PlaceName AS rue,
    L.CityName AS commune,
    CC.ccArchiveTypeConst,
    CC.Identifier,
    CC.ccCallcardUid,
    --CC.ccPhoneCallUid,
    PC.ccPhoneCallUid,
    PC.ccCallcardUid,
    FORMAT( PCS.CreationUtc, 'HH:mm:ss' ),
    PCS.ccPhoneCallUid,
    PCS.ccPhoneCallStateConst,
    PCS.CreationUtc,
    FORMAT( DATEADD( hour, 1, PCS.CreationUtc ), 'HH:mm:ss' ) AS heure_prise_appel,
    TA.ccArchiveTypeConst,
    TA.Name AS type_archive
FROM
    ccEvents E
             
        INNER JOIN ccLocations L
            ON E.ccCallcardUid = L.ccCallcardUid
        INNER JOIN ccCallcards CC
            ON E.ccCallcardUid = CC.ccCallcardUid
        INNER JOIN ccArchiveTypes TA
            ON CC.ccArchiveTypeConst = TA.ccArchiveTypeConst
        INNER JOIN ccPhoneCalls PC
            ON E.ccCallcardUid = PC.ccCallcardUid
     
        INNER JOIN (
                -- CallCard la plus ancienne
                SELECT
                    ccCallcardUid, 
                    MIN ( CreationUtc ) AS CreationUtc
                FROM ccCardStamps
                WHERE Complement = 'Véhicule non engagé'
                GROUP BY ccCallcardUid
            ) MCS
            ON E.ccCallcardUid = MCS.ccCallcardUid
        INNER JOIN ccCardStamps CS
            ON MCS.ccCallcardUid = CS.ccCallcardUid
            AND MCS.CreationUtc = CS.CreationUtc
             
        INNER JOIN (
                -- PhoneCallStamps la plus ancienne
                SELECT
                    ccPhoneCallUid, 
                    MIN( CreationUtc ) AS CreationUtc
                FROM ccPhoneCallStamps
                WHERE ccPhoneCallStateConst = '1'
                GROUP BY ccPhoneCallUid
            ) MPCS
            ON PC.ccPhoneCallUid = MPCS.ccPhoneCallUid
        INNER JOIN ccPhoneCallStamps PCS
            ON MPCS.ccPhoneCallUid = PCS.ccPhoneCallUid
            AND MPCS.CreationUtc = PCS.CreationUtc
WHERE
    E.CreationUtc BETWEEN @DateFrom AND DATEADD( hour, 24, @DateTo )
    AND TA.Name = 'Normal'
ORDER BY E.CreationUtc DESC;

Et voici ce qui en ressort, tu peux constater que j'ai des ccCallcardUid qui se répètent:

-
Edité par ValterJeronimo1 19 février 2021 à 14:43:27

Benzouye

19 février 2021 à 15:46:03

Donc les cardinalités ne sont pas celles annoncées, il y a encore autre chose ... en plus sur la copie d'écran on ne voit pas les colonnes qui différencient les lignes "identiques" ...

Peux-tu écrire pour chaque entité à combien d'enregistrement elle peut être liée à chaque autre entité ?

Benzouye a écrit:

quelles sont les cardinalités entre les tables en jeu ? 1 callcard = plusieurs events ? 1 phoneCall = plusieurs phoneCallStamps ?

Faire ce travail mais pour toutes les relations possibles ... En l'état je ne peux rien deviner.

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

19 février 2021 à 17:01:41

C'est celui-ci qui me crée plusieurs lignes en fait.

 FORMAT( DATEADD( hour, 1, PCS.CreationUtc ), 'HH:mm:ss' ) AS heure_prise_appel,

Car pour chaque appel différent il y a un Id ( PCS.ccPhoneCallUid ).

Donc si je suis ton raisonnement, je dois lister tous les PCS.ccPhoneCallUid liés au même E.ccCallcardUid et afficher uniquement le plus ancien. Mais du coup je ne peux pas faire un Group By PCS.ccPhoneCallUid puisqu'ils sont tous différents.

Je dois faire une jonction entre

PC.ccPhoneCallUid et PCS.ccPhoneCallUid

Puis une jonction entre

PC.ccCallcardUid et E.ccCallcardUid

Et ensuite afficher uniquement le PCS.ccPhoneCallUid plus ancien avec un LIMIT 1

Je suis dans le juste ?

Benzouye

19 février 2021 à 17:11:35

ValterJeronimo1 a écrit:

je dois lister tous les PCS.ccPhoneCallUid liés au même E.ccCallcardUid et afficher uniquement le plus ancien. Mais du coup je ne peux pas faire un Group By PCS.ccPhoneCallUid puisqu'ils sont tous différents

Cela signifie que pour un ccCallCardUid tu as plusieurs ccPhoneCallUid, et que pour un ccPhoneCallUid tu as plusieurs ccPhoneCallStamps.

Ma requête ne traitait que la deuxième partie, pas la première ...

Il faut traiter les deux, donc déterminer quel est le plus ancien ccPhoneCallUid pour chaque ccCallCardUid, donc encore une sous-requête ...

Si la table ccPhoneCalls a aussi une colonne CreateUtc, ce serait donc :

SELECT
	E.ccCallcardUid,
	E.EventText,
	E.CreationUtc,
	FORMAT( E.CreationUtc, 'dd.MM.yyyy - HH:mm:ss' ) AS utc,
	CS.Complement AS Complement,
	CS.ccStampTypeConst AS code_alarme,
	CS.ccStatusConst AS code_alarme_carte,
	FORMAT( DATEADD( hour, 1, CS.CreationUtc), 'HH:mm:ss') AS heure_alarme,
	L.geoSectorId AS secteur,
	L.StreetNumber AS numero,
	L.PlaceName AS rue,
	L.CityName AS commune,
	CC.ccArchiveTypeConst,
	CC.Identifier,
	CC.ccCallcardUid,
	PC.ccPhoneCallUid,
	PC.ccCallcardUid,
	FORMAT( PCS.CreationUtc, 'HH:mm:ss' ),
	PCS.ccPhoneCallUid,
	PCS.ccPhoneCallStateConst,
	PCS.CreationUtc,
	FORMAT( DATEADD( hour, 1, PCS.CreationUtc ), 'HH:mm:ss' ) AS heure_prise_appel,
	TA.ccArchiveTypeConst,
	TA.Name AS type_archive
FROM
	ccEvents E
			  
		INNER JOIN ccLocations L
			ON E.ccCallcardUid = L.ccCallcardUid
		INNER JOIN ccCallcards CC
			ON E.ccCallcardUid = CC.ccCallcardUid
		INNER JOIN ccArchiveTypes TA
			ON CC.ccArchiveTypeConst = TA.ccArchiveTypeConst
	  
		INNER JOIN (
				-- CallCard la plus ancienne
				SELECT
					ccCallcardUid,
					MIN ( CreationUtc ) AS CreationUtc
				FROM ccCardStamps
				WHERE Complement = 'Véhicule non engagé'
				GROUP BY ccCallcardUid
			) MCS
			ON E.ccCallcardUid = MCS.ccCallcardUid
		INNER JOIN ccCardStamps CS
			ON MCS.ccCallcardUid = CS.ccCallcardUid
			AND MCS.CreationUtc = CS.CreationUtc
			  
		INNER JOIN (
				-- PhoneCall le plus ancien
				SELECT
					ccCallcardUid,
					MIN( CreationUtc ) AS CreationUtc
				FROM ccPhoneCalls
				GROUP BY ccCallcardUid
			) MPC
			ON E.ccCallcardUid = MPC.ccCallcardUid
		INNER JOIN ccPhoneCalls PC
			ON MPC.ccCallcardUid = PC.ccCallcardUid
			AND MPC.CreationUtc = PC.CreationUtc
			  
		INNER JOIN (
				-- PhoneCallStamps la plus ancienne
				SELECT
					ccPhoneCallUid,
					MIN( CreationUtc ) AS CreationUtc
				FROM ccPhoneCallStamps
				WHERE ccPhoneCallStateConst = '1'
				GROUP BY ccPhoneCallUid
			) MPCS
			ON PC.ccPhoneCallUid = MPCS.ccPhoneCallUid
		INNER JOIN ccPhoneCallStamps PCS
			ON MPCS.ccPhoneCallUid = PCS.ccPhoneCallUid
			AND MPCS.CreationUtc = PCS.CreationUtc
WHERE
	E.CreationUtc BETWEEN @DateFrom AND DATEADD( hour, 24, @DateTo )
	AND TA.Name = 'Normal'
ORDER BY E.CreationUtc DESC;

-
Edité par Benzouye 19 février 2021 à 17:33:16

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

ValterJeronimo1

19 février 2021 à 17:23:15

Huum, ça pose encore problème. Mais je vois bien la logique. C'est compliqué pour moi de plus te détailler les entité reliées et relations existantes. j'ai récupéré cette base de données que je n'ai pas crée, et je la découvre.

Je vais essayer de me débrouiller avec tes précieuses informations.

Je reviendrai vers toi si besoin ou pour clore le sujet.

Merci en tout cas.

Benzouye

19 février 2021 à 17:31:39

Attention j'ai fais une erreur de jointure, je corrige mon message précédent ligne 60/61.

-
Edité par Benzouye 22 février 2021 à 9:18:52

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL