DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2015/01/18 00:00:00';
SET @EndDate = '2015/10/30 23:59:59';
SELECT SUM(CASE WHEN t.Size < 1024*1024 THEN 1
ELSE 0
END) AS [< 1MB]
, SUM(CASE WHEN t.Size >= 1024*1024 AND t.Size < 5*1024*1024 THEN 1
ELSE 0
END) AS [1MB <= X < 5MB]
, SUM(CASE WHEN t.Size >= 5*1024*1024 AND t.Size < 10*1024*1024 THEN 1
ELSE 0
END) AS [5MB <= X < 10MB]
, SUM(CASE WHEN t.Size >= 10*1024*1024 AND t.Size < 20*1024*1024 THEN 1
ELSE 0
END) AS [10MB <= X < 20MB]
, SUM(CASE WHEN t.Size >= 20*1024*1024 THEN 1
ELSE 0
END) AS [X >= 20MB]
FROM Traffic t INNER JOIN MessageList m ON t.TrafficId = m.TrafficId
INNER JOIN RecipientList r ON m.RecipientListId = r.RecipientListId
WHERE t.LogDate >= @StartDate
AND t.LogDate <= @EndDate
--AND t.[Sent] = 1
--AND dbo.IsInternalEmail(t.Sender) = 1
-- Sent
AND dbo.IsInternalEmail(t.Sender) > 0
AND (
( t.Sent = 1
AND dbo.IsInternalEmail(r.Recipient) > 0
)
OR
(
dbo.IsInternalEmail(r.Recipient) <= 0
)
)