DECLARE @StartPastDays INT
SET @StartPastDays = 1;
DECLARE @PastDays INT
SET @PastDays = 7;
DECLARE @StartDate NVARCHAR(64)
DECLARE @EndDate NVARCHAR(64)
SET @StartDate = CAST(CONVERT( VARCHAR(10), DATEADD(day, -(@PastDays+@StartPastDays-1), CONVERT(VARCHAR(10),GETDATE(),111)), 111 ) + ' 00:00:00' AS NVARCHAR)
PRINT @StartDate;
SET @EndDate = CAST(CONVERT( VARCHAR(10), DATEADD(day, -@StartPastDays, CONVERT(VARCHAR(10),GETDATE(),111)), 111 ) + ' 23:59:59' AS NVARCHAR)
PRINT @EndDate;
DECLARE @DomainList AS TABLE (Domain NVARCHAR(256))
INSERT INTO @DomainList (Domain)
VALUES ('yourdomainA.com'), ('yourdomainB.com'), ('yourdomainC.com')
SELECT t.Sender
, r.Recipient
, COUNT(*) AS [Received Count]
FROM Traffic t INNER JOIN MessageList m ON t.TrafficId = m.TrafficId
INNER JOIN RecipientList r ON m.RecipientListId = r.RecipientListId
WHERE --dbo.IsInternalEmailByDomain(t.Sender, t.SenderDomain) = 0
--AND dbo.IsInternalEmailByDomain(r.Recipient, r.Domain) = 1
-- * Internal Receveid from Internal
EXISTS (SELECT 1
FROM @DomainList d
WHERE t.SenderDomain = d.Domain
)
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE r.Domain = d.Domain
)
-- Received
--AND dbo.IsInternalEmail(r.Recipient) > 0
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE r.Domain = d.Domain
)
AND (
( t.Sent = 0
--AND dbo.IsInternalEmail(t.Sender) > 0
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE t.SenderDomain = d.Domain
)
)
--OR
--(
-- --dbo.IsInternalEmail(t.Sender) <= 0
-- NOT EXISTS (SELECT 1
-- FROM @DomainList d
-- WHERE t.SenderDomain = d.Domain
-- )
--)
)
AND t.LogDate >= @StartDate
AND t.LogDate < @EndDate
GROUP BY r.Recipient --, t.SenderDomain
SELECT t.Sender
, r.Recipient
, COUNT(*) AS [Sent Count]
FROM Traffic t INNER JOIN MessageList m ON t.TrafficId = m.TrafficId
INNER JOIN RecipientList r ON m.RecipientListId = r.RecipientListId
WHERE --dbo.IsInternalEmailByDomain(t.Sender, t.SenderDomain) = 1
--AND dbo.IsInternalEmailByDomain(r.Recipient, r.Domain) = 0
-- * Internal to Internal
EXISTS (SELECT 1
FROM @DomainList d
WHERE t.SenderDomain = d.Domain
)
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE r.Domain = d.Domain
)
-- Sent
-- AND dbo.IsInternalEmail(t.Sender) > 0
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE t.SenderDomain = d.Domain
)
AND (
( t.Sent = 1
--AND dbo.IsInternalEmail(r.Recipient) > 0
AND EXISTS (SELECT 1
FROM @DomainList d
WHERE r.Domain = d.Domain
)
)
--OR
--(
-- --dbo.IsInternalEmail(r.Recipient) <= 0
-- NOT EXISTS (SELECT 1
-- FROM @DomainList d
-- WHERE r.Domain = d.Domain
-- )
--)
)
AND t.LogDate >= @StartDate
AND t.LogDate < @EndDate
GROUP BY t.Sender --, r.Domain