SELECT [LogDate]
,[TrafficType]
,[Server]
,[Detail]
, COUNT(*) AS [Count]
FROM [Mailscape].[dbo].[TrafficStatus]
WHERE TrafficType = 'Message' AND Detail = 1
GROUP BY LogDate, [Server], TrafficType, Detail Having COUNT(*) > 1
ORDER BY LogDate, [Server]
SELECT *
FROM TrafficStatus
WHERE TrafficType = 'Message' AND Detail = 1
AND LogDate IN (
SELECT [LogDate]
FROM [Mailscape].[dbo].[TrafficStatus]
WHERE TrafficType = 'Message' AND Detail = 1
GROUP BY LogDate, [Server], TrafficType, Detail Having COUNT(*) > 1
)
ORDER BY LogDate, [Server]
/*******************************************/
SELECT DISTINCT [LogDate]
,[Server]
FROM [Mailscape].[dbo].[TrafficStatus] ts
WHERE TrafficType = 'Message' AND Detail = 1
AND NOT EXISTS (
SELECT DISTINCT [LogDate]
, [Server]
FROM [Mailscape].[dbo].[TrafficStatus]
WHERE TrafficType = 'Message' AND Detail = 0
AND [LogDate] = ts.[LogDate]
AND [Server] = ts.[Server]
--GROUP BY [LogDate], [Server]
)
ORDER BY LogDate, [Server]
SELECT [LogDate]
, COUNT([Server]) AS [# of Servers]
, COUNT(DISTINCT [Server]) AS [# of Distinct Servers]
FROM [Mailscape].[dbo].[TrafficStatus] ts
WHERE TrafficType = 'Message' AND Detail = 1
GROUP BY [LogDate]
ORDER BY [LogDate]