DECLARE @StartPastDays INT
SET @StartPastDays = 1;
DECLARE @PastDays INT
SET @PastDays = 180;
DECLARE @date DATETIME
SET @date = '20000101'
DECLARE @days INT
SET @days = DATEDIFF(day, @date, GETDATE())
DECLARE @StartDate DATETIME
SET @StartDate = DATEADD(day, @days-(@PastDays+@StartPastDays-1), @date)
PRINT @StartDate
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(day, @days-@StartPastDays+1+1, @date)
PRINT @EndDate
SELECT name As 'Distribution List', mail As 'EMail Address', 0 As 'Message Count'
FROM openquery
(
ADSI, '<LDAP://{DomainController}>;(|(&(objectCategory=group)(samAccountType=268435457))(objectClass=msExchDynamicDistributionList)(&(objectClass=group)(proxyAddresses=*)));name,mail;subtree'
)
WHERE mail NOT IN (
SELECT r.Recipient
FROM RecipientList r INNER JOIN MessageList m ON m.RecipientListId = r.RecipientListId
INNER JOIN Traffic t ON t.TrafficId = m.TrafficId
WHERE t.Sent = 0
-- AND t.LogDate >= DATEADD([day], -90, GETDATE())
AND (t.LogDate >= @StartDate AND t.LogDate <= @EndDate)
)
ORDER BY name ASC