count the amount of mails per day and the highest amount of emails sent in an hour for that day-CodePudding

I have a table that every email sent goes into. I am trying to get the count of emails sent per day (which the below script works for) but as well as that I want to show the highest amount of emails sent in any one hour during that day. For example 30222 emails for 1st July but the highest sent in any one hour is 5567

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, completedAt)) [Day of Month],
       COUNT(*) [Number of Emails in day]
FROM [MailDB].[dbo].[Email] WITH (NOLOCK)
WHERE completedAt BETWEEN '01 Jun 22' AND '01 Sep 22';

Could someone give me a bit of guidance please?

I have tried adding an outer apply select but don't seem to be getting that anywhere near correct.

CodePudding user response：

You just need to group and group again

SELECT
  date,
  SUM(countEmails) [Number of Emails in day],
  MAX(countEmails) maxEmailsPerHour
FROM (
    SELECT
      date = CAST(e.completedAt AS date),
      countEmails = COUNT(*)
    FROM dbo.Email e
    WHERE completedAt >= '20220601'
      AND completedAt  < '20220901'
    GROUP BY
      CAST(e.completedAt AS date),
      DATEPART(HOUR, e.completedAt)
) e
GROUP BY
  date;

If you also want to know which hour that is then you need a ROW_NUMBER

SELECT
  date,
  [Number of Emails in day] = SUM(countEmails),
  maxEmailsPerHour = MAX(countEmails),
  maxHour = MAX(CASE WHEN rn = 1 THEN hour END)
FROM (
    SELECT
      date = CAST(e.completedAt AS date),
      hour = DATEPART(HOUR, e.completedAt),
      countEmails = COUNT(*),
      rn = ROW_NUMBER() OVER (PARTITION BY CAST(e.completedAt AS date) ORDER BY COUNT(*) DESC)
    FROM dbo.Email e
    WHERE completedAt >= '20220601'
      AND completedAt  < '20220901'
    GROUP BY
      CAST(e.completedAt AS date),
      DATEPART(HOUR, e.completedAt)
) e
GROUP BY
  date;

CodePudding user response：

First aggregate to the hour, and then you can get the SUM and MAX for that day:

WITH CTE AS(
    SELECT CONVERT(date,completedAt) AS EmailDate,
           DATEPART(HOUR,completedAt) AS EmailHour,
           COUNT(*) AS Emails
    FROM dbo.Email --I doubt you need NOLOCK. Do you understand what it actually does?
    WHERE completedAt >= '20220601' -- I assume this is actually what you need. 
      AND completedAt < '20220901' -- BETWEEN doesn't work how people "think" it does with dates.
    GROUP BY CONVERT(date,completedAt),
             DATEPART(HOUR,completedAt))
SELECT EmailDate,
       SUM(Emails) AS EmailsSent,
       MAX(Emails) AS MostInAnHour
FROM CTE
GROUP BY EmailDate;