SQL Query to get mail queue data

SQL Query to get mail queue xml data in stored procedure TMCS_SHOW_EMAIL_QUEUE_HISTORY

SELECT DISTINCT 
mailing.TITLE AS distributiontitle,
mailing.EMAIL_SUBJECT AS emailsubject, 
distributionlists.TITLE AS distribution, 
DATEADD(day, 0, DATEDIFF(day, 0, emails.DATE_SENT)) AS datesent, 
COUNT(emails.ID) AS totalindistribution, 
MIN(emails.DATE_SENT) AS earliest, 
MAX(emails.DATE_SENT) AS latest, 
DATEDIFF(ss, MIN(emails.DATE_SENT), MAX(emails.DATE_SENT)) AS processingtime, 
COUNT(emails.ID) / (1 + DATEDIFF(ss, MIN(emails.DATE_SENT), MAX(emails.DATE_SENT))) AS emailspersecond
FROM         
dbo.MAILINGS AS mailing INNER JOIN
dbo.EMAILS AS emails ON mailing.ID = emails.MAILING_ID INNER JOIN
dbo.MAILINGS_DISTRIBUTIONLISTS AS md ON mailing.ID = md.MAILING_ID INNER JOIN
dbo.DISTRIBUTIONLISTS AS distributionlists ON md.DISTRIBUTIONLIST_ID = distributionlists.ID
WHERE     
(emails.DATE_SENT > DATEADD(day, 0, DATEDIFF(day, 3, GETDATE())))
GROUP BY 
DATEADD(day, 0, DATEDIFF(day, 0, emails.DATE_SENT)), 
mailing.TITLE, 
mailing.EMAIL_SUBJECT, 
distributionlists.TITLE
ORDER BY 
earliest DESC, 
datesent DESC, 
distributiontitle, 
emailsubject, 
distribution
FOR XML AUTO, ELEMENTS, ROOT('root')

My Tridion Newsletters

SDL Tridion Nominate your MVP Candidate

Sign up and get a newsletter when new Tridion stuff is added to the site

Nominate

SDL Tridion Nominate your MVP Candidate

If you find the content of this page useful, please nominate me for the SDL Tridion MVP award.


Created: Thursday 27th May 2010 11:27 PM
Last Modified: Wednesday 30th June 2010 8:22 PM LinkedIn link: View Chris Mills' profile on LinkedIn
Valid XHTML 1.0! Valid CSS! Level A conformance icon, W3C-WAI Web Content Accessibility Guidelines 1.0