Requete sur une correspondance

[panza]

Bonjour,
Je ne m'en sors pas de ma requete SQL (server 2008) pour la procedure stockée (et pourtant c'est simple).


J'ai une table de messages avec les colonnes :
"Expéditeur / Destinataire / Date / Message"

Je peux donc avoir plusieurs lignes dedans comme :
"Expéditeur / Destinataire / Date / Message"
---------------------------------------
Pierre / Marc / 15 mai / xxx
Pierre/ Marc / 17 mai / yyy
ou même à l'envers si j'ai :
Marc / Pierre / 16 mai / zzz
(Car ce n'est pas toujours Pierre qui écrit à Marc, Marc peut aussi écrire à Pierre.
Tout comme Pierre peut aussi ecrire à Simon :
Pierre / Simon / 2 juin / nnn

Ce qui m'intéresse, c'est de faire une requete qui me donne la page de "correspondance" de Pierre. Donc juste les correspondances de pierre avec les autres ou des autres avec pierre.

En sortie je voudrais une table aux colonnes :
"Date / Correspondant / Nb Messages"

Donc si je prends les données de ce que j'ai cité, ca devrait me rendre :

"Date / Correspondant / Nb Messages"
------------------------------------------

2 juin / Simon / 1
17 mai / Marc / 3


Merci de votre aide

[7gyY9w1ZY6ySRgPeaefZ]

Code :

1
2
3
4
5
6
7
8
9
WITH SR AS (
	SELECT Expéditeur AS usager1, Destinataire AS usager2, Date FROM ma_table
	union ALL 
	SELECT Destinataire AS usager1, Expéditeur AS usager2, Date FROM ma_table
)
SELECT date, usager1 usager_recherche, usager2 usager_correspondant, count(*) AS [Nb Messages]
FROM SR 
WHERE usager1 = 'Pierre'
GROUP BY date, usager1, usager2

Je n'ai pas testé et j'ai l'impression de faire compliqué mais ça devrait répondre à ton besoin, tout en étant une requête généralisable pour tous les usagers si tu retires la clause where.

À noter que je n'ai pas gérer le cas où Machin s'écrit à lui-même...

[mikedavem]

Bonjour,

Une solution plus simple peut etre ?

Code :

1
2
3
4
5
6
7
SELECT 
 expediteur, destinataire, date_operation, COUNT(*) AS nb_message
FROM dbo.maTable
WHERE expediteur = 'Pierre'
 OR destinataire = 'Pierre'
GROUP BY expediteur, destinataire, date_operation
ORDER BY date_operation;

++

[SLE]

Envoyé par mikedavem

Bonjour,

Une solution plus simple peut etre ?

Code :

++

Et pour avoir le résultat sur 3 champs, comme demandé :

Code :

1
2
3
4
5
6
7
SELECT 
 case when expediteur = 'Pierre' then destinataire else expediteur end AS Correspondant, date_operation, COUNT(*) AS nb_message
FROM dbo.maTable
WHERE expediteur = 'Pierre'
 OR destinataire = 'Pierre'
GROUP BY case when expediteur = 'Pierre' then destinataire else expediteur end, date_operation
ORDER BY date_operation;

[mikedavem]

Bon globalement on pourrait aussi voir s'il est pas intéressant de faire des union all pour pouvoir profiter d'éventuels index de la table au lieu d'avoir des prédicats à base de OR.

++

[panza]

Merci pour vos réponse, on approche du resultat meme si c'est pas encore ca.

En prenant la technique de "7gyY9w1ZY6ySRgPeaefZ", la requete adaptée est :

Code :

1
2
3
4
5
6
7
8
9
	WITH sr AS (
	SELECT 	Message.IDSource AS usager1, Message.IDDest AS usager2, message.DateWrite AS madate, COUNT(*) AS compte FROM dbo.Message GROUP BY Message.IDSource, Message.IDDest, message.DateWrite
	union ALL
	SELECT 	Message.IDDest AS usager1, Message.IDSource AS usager2, message.DateWrite AS madate, COUNT(*)AS compte  FROM dbo.Message GROUP BY Message.IDSource, Message.IDDest, message.DateWrite
	)
	SELECT  madate, usager2, count(*) AS NBMessages FROM sr 
	WHERE usager1 = 'pierre'
	GROUP BY     usager2 , madate
	ORDER BY madate DESC

Mais cela me répond toutes les lignes de correspondant avec le meme correspondant, le resultat des correspondances de pierre est :

date/usager2/Nbmessages
-----------------------------
17 jan / emilie / 1
16 jan / emilie / 1
15 jan / emilie / 1
5 jan / marcel / 1
1 jan / francis / 1

En, fait j'aimerai avoir :

17 jan / emilie / 3
5 jan / marcel / 1
1 jan / francis / 1

En prenant le modele de SLE la requete est :

Code :

1
2
3
4
5
	SELECT 
	case when Message.IDSource = 'pierre' then Message.IDDest else Message.IDSource end AS correspondant, message.DateWrite, COUNT(*) FROM dbo.Message
	WHERE Message.IDSource = 'pierre' OR Message.IDDest='pierre'
	GROUP BY case when Message.IDSource='pierre' then Message.IDDest else Message.IDSource end, Message.DateWrite
	ORDER BY  Message.DateWrite DESC

Le résultat est :

Correspondant / Date / NBmess
------------------------------
emilie / 17 jan / 1
emilie / 16 jan / 1
emilie / 15 jan / 1
marcel / 5 jan / 1
francis / 2 jan / 1

Et donc je voudrais avoir :

emilie / 17 jan / 3
marcel / 5 jan / 1
francis / 2 jan / 1

Merci pour vos nouveaux conseils.

[SLE]

Envoyé par panza

Le résultat est :
Correspondant / Date / NBmess
------------------------------
emilie / 17 jan / 1
emilie / 16 jan / 1
emilie / 15 jan / 1
marcel / 5 jan / 1
francis / 2 jan / 1

Et donc je voudrais avoir :
emilie / 17 jan / 3
marcel / 5 jan / 1
francis / 2 jan / 1

Comme ceci

Code :

1
2
3
4
5
6
7
8
SELECT * FROM
(
SELECT
case when Message.IDSource = 'pierre' then Message.IDDest else Message.IDSource end AS correspondant, max(message.DateWrite) AS DateWrite, COUNT(*) FROM dbo.Message
WHERE Message.IDSource = 'pierre' OR Message.IDDest='pierre'
GROUP BY case when Message.IDSource='pierre' then Message.IDDest else Message.IDSource end
) AS tmp
ORDER BY DateWrite DESC

[panza]

Excellent SLE !
Merci

[SLE]

Juste pour info, pour mettre la discussion en Résolu, il ne faut pas éditer le titre mais cliquer sur le bouton "Résolu"

[panza]

Ben ca tombe bien car en fait ce n'est pas résolu...
Pour ce que j'ai énoncé, c'est bon, j'ai bien :

emilie / 17 jan / 3
marcel / 5 jan / 1
francis / 2 jan / 1

Mais dans ma table de messages j'ai d'autres champs :
Lu : champ date qui contient la date a laquelle le message a été lu. Reste à 'null' si non lu

Dans le résultat j'ai besoin d'une autre colonn epour dire si le message a été lu.
Si le dernier n'est pas lu (par exemple dans la derniere réponse de emilie), je veux que cela apparaisse avec un lu à 0 ou un nonlu à 1
Cela me permettra de mettre la ligne de ce correspondant en surbrillance, s'il reste un msg a lire.

Merci

[7gyY9w1ZY6ySRgPeaefZ]

Envoyé par SLE

Comme ceci

Code :

pourquoi faire une sous-requête là ?
Autant faire simplement :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT
	case 
		when Message.IDSource = 'pierre' then Message.IDDest 
		else Message.IDSource end AS correspondant, 
	max(message.DateWrite) AS DateWrite, 
	COUNT(*) Quantite
FROM dbo.Message
WHERE Message.IDSource = 'pierre' 
	OR Message.IDDest='pierre'
GROUP BY case when Message.IDSource='pierre' then Message.IDDest else Message.IDSource end
ORDER BY max(message.DateWrite) DESC

[panza]

Merci
Mais ca ne résoud pas mon probleme de faire apparaitre si une corresponance contient un ou plusieurs messages non lus.
Quelle solution adopter ?
Merci de votre aide

[7gyY9w1ZY6ySRgPeaefZ]

Code :

1
2
3
4
5
6
7
8
9
WITH SR AS (
	SELECT Expéditeur AS usager1, Destinataire AS usager2, Date, case when date_lue IS NULL then 1 else 0 end AS lu FROM ma_table
	union ALL 
	SELECT Destinataire AS usager1, Expéditeur AS usager2, Date, case when date_lue IS NULL then 1 else 0 end AS lu FROM ma_table
)
SELECT date, usager1 usager_recherche, usager2 usager_correspondant, count(*) AS [Nb Messages] , sum(lu) nbre_courriels_lus
FROM SR 
WHERE usager1 = 'Pierre'
GROUP BY date, usager1, usager2

[panza]

Si je retranscris ton code avec mes noms de champ cela donne :

Code :

1
2
3
4
5
6
7
8
9
WITH SR AS (
	SELECT Message.IDSource AS usager1, Message.IDDest AS usager2, message.DateWrite, case when message.DateRead IS NULL then 1 else 0 end AS lu FROM dbo.Message
	union ALL 
	SELECT Message.IDDest  AS usager1,  Message.IDSource AS usager2, message.DateWrite, case when message.DateRead IS NULL then 1 else 0 end AS lu FROM dbo.Message
)
SELECT DateWrite, usager1 usager_recherche, usager2 usager_correspondant, count(*) AS [Nb Messages] , sum(lu) nbre_courriels_lus
FROM SR 
WHERE usager1 = 'Pierre'
GROUP BY DateWrite, usager1, usager2

Mais la ca ne va pas du tout. Le résultat liste toutes les lignes :

date                                  u1                u2      nbMes  nbLus     
2011-01-15 00:00:00.000	pierre	emilie	1	0
2011-01-17 00:00:00.000	pierre	emilie	1	0
2011-01-18 00:00:00.000	pierre	emilie	1	1
2011-01-01 00:00:00.000	pierre	francis	1	1
2011-01-05 00:00:00.000	pierre	marcel	1	0

[7gyY9w1ZY6ySRgPeaefZ]

Dites donc, rien ne vous empêche aussi de travailler de votre côté ce qui a été mis à votre disposition, au lieu d'attendre que ça vous tombe tout cru dans le bec...

Code :

1
2
3
4
5
6
7
8
9
WITH SR AS (
SELECT Message.IDSource AS usager1, Message.IDDest AS usager2, message.DateWrite, case when message.DateRead IS NULL then 1 else 0 end AS lu FROM dbo.Message
union ALL
SELECT Message.IDDest AS usager1, Message.IDSource AS usager2, message.DateWrite, case when message.DateRead IS NULL then 1 else 0 end AS lu FROM dbo.Message
)
SELECT max(DateWrite) max_datewrite, usager1 usager_recherche, usager2 usager_correspondant, count(*) AS [Nb Messages] , sum(lu) nbre_courriels_lus
FROM SR
WHERE usager1 = 'Pierre'
GROUP BY usager1, usager2