DECLARE @StartDate DATETIME = '20130101'
PRINT @StartDate
DECLARE @EndDate DATETIME = '20141001'
PRINT @EndDate
SET @EndDate = DATEADD(day, 1, @EndDate);
PRINT @EndDate
SELECT YEAR(t.LogDate) AS [Year]
, COUNT(m.MessageId) AS [Count]
FROM Traffic t INNER JOIN MessageList m ON t.TrafficId = m.TrafficId
INNER JOIN RecipientList r ON r.RecipientListId = m.RecipientListId
WHERE
(
-- internal sender
(t.[Sent] = 1 AND dbo.IsInternalEmail(t.Sender) > 0)
OR
-- external sender
(t.[Sent] = 0 AND dbo.IsInternalEmail(t.Sender) <= 0)
)
AND r.Recipient IN ('spam@yourdomain.com', 'junk@office365.microsoft.com')
AND (t.LogDate >= @StartDate AND t.LogDate < @EndDate)
GROUP BY YEAR(t.LogDate)
ORDER BY [Year]