1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
SELECT
dbo.MAILS_ENVOIS.ID_ARTICLE,
COUNT(*) AS NBDEST,
COUNT(M1.mailitem_id) AS NBENVOIS,
COUNT(M2.mailitem_id) AS NBENCOURS,
COUNT(M3.mailitem_id) AS NBECHEC
FROM
dbo.STRIKE
INNER JOIN dbo.MAILS_ENVOIS ON dbo.STRIKE.ID = dbo.MAILS_ENVOIS.IDSTRIKE
LEFT JOIN msdb.dbo.sysmail_sentitems M1 ON M1.mailitem_id = dbo.MAILS_ENVOIS.IDMAIL
LEFT JOIN msdb.dbo.sysmail_unsentitems M2 ON M2.mailitem_id = dbo.MAILS_ENVOIS.IDMAIL
LEFT JOIN msdb.dbo.sysmail_faileditems M3 ON M3.mailitem_id = dbo.MAILS_ENVOIS.IDMAIL
WHERE
IDMAIL IS NOT NULL
GROUP BY
dbo.MAILS_ENVOIS.ID_ARTICLE |