DECLARE @StartDate DATETIME = '20140101'
PRINT @StartDate
DECLARE @EndDate DATETIME = '20141001'
PRINT @EndDate
SET @EndDate = DATEADD(day, 1, @EndDate);
PRINT @EndDate
DECLARE @data TABLE ([Year] INT, [Week] INT, [Count] BIGINT, [Order] INT)
INSERT INTO @data ([Year], [Week], [Count], [Order])
SELECT YEAR(t.LogDate) AS [Year]
, DATEPART(WEEK, t.LogDate) AS [Week]
, COUNT(m.MessageId) AS [Count]
, YEAR(t.LogDate)*100 + DATEPART(WEEK, t.LogDate) AS [Order]
FROM Traffic t INNER JOIN MessageList m ON t.TrafficId = m.TrafficId
INNER JOIN RecipientList r ON r.RecipientListId = m.RecipientListId
WHERE
(
-- internal sender
(t.[Sent] = 1 AND dbo.IsInternalEmail(t.Sender) > 0)
OR
-- external sender
(t.[Sent] = 0 AND dbo.IsInternalEmail(t.Sender) <= 0)
)
AND r.Recipient IN ('spam@yourdomain.com', 'junk@office365.microsoft.com')
AND (t.LogDate >= @StartDate AND t.LogDate < @EndDate)
GROUP BY YEAR(t.LogDate), DATEPART(WEEK, t.LogDate)
--ORDER BY [Year], [Week]
--PRINT CAST( (CAST([Year] AS NVARCHAR) + '-01-01') AS DATETIME )
SELECT [Year], [Week], [Week Start], [Week End], [Count]
FROM (
SELECT CAST([Year] AS NVARCHAR) AS [Year]
, CAST([Week] AS NVARCHAR) AS [Week]
, CAST(CAST( Dateadd(dd, 1-( Datepart(dw, Dateadd(wk, [Week], CAST( (CAST([Year] AS NVARCHAR) + '-01-01') AS DATETIME ) )) ), Dateadd(wk, [Week], CAST( (CAST([Year] AS NVARCHAR) + '-01-01') AS DATETIME ) )) AS DATE) AS NVARCHAR) AS [Week Start]
, CAST(CAST( Dateadd(dd, 7 - ( Datepart(dw, Dateadd(wk, [Week], CAST( (CAST([Year] AS NVARCHAR) + '-01-01') AS DATETIME ) )) ), Dateadd(wk, [Week], CAST( (CAST([Year] AS NVARCHAR) + '-01-01') AS DATETIME ) )) AS DATE) AS NVARCHAR) AS [Week End]
, [Count]
, [Order]
FROM @data
UNION ALL
SELECT '' AS [Year]
, '' AS [Week]
, '' AS [Week Start]
, 'Total' AS [Week End]
, SUM([Count]) AS [Count]
, 999900 AS [Order]
From @data
) A
ORDER BY [Order]