DECLARE @StartDate DATE --NVARCHAR(64)
DECLARE @EndDate DATE --NVARCHAR(64)
SET @StartDate = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0));
SET @EndDate = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1));
SET @EndDate = DATEADD(day, 1, @EndDate);
PRINT @StartDate
PRINT @EndDate
SELECT C.Sender
, C.[Total Sent]
, D.Recipient AS [Internal Recipient]
, D.[Total Sent Per Recipient]
FROM (
SELECT Sender
, SUM([Count]) As [Total Sent]
FROM (
SELECT t.Sender
, r.Recipient
, COUNT(*) AS [Count]
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
-- sent to internal
AND dbo.IsInternalEmail(t.Sender) = 1
AND dbo.IsInternalEmail(r.Recipient) = 1
GROUP BY t.Sender, r.Recipient
) A
GROUP BY A.Sender
) C INNER JOIN (
SELECT t.Sender
, r.Recipient
, COUNT(*) AS [Total Sent Per Recipient]
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
-- sent to internal
AND dbo.IsInternalEmail(t.Sender) = 1
AND dbo.IsInternalEmail(r.Recipient) = 1
GROUP BY t.Sender, r.Recipient
) D ON C.Sender = D.Sender
ORDER BY C.Sender