Sorry, took a bit longer than I expected.
I think you're after something like this: -
DECLARE @WEEKS INT;
SET @WEEKS = 12;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, b.Total
FROM TALLY a
OUTER APPLY (SELECT COUNT(1)
FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN a.X AND a.N) b(Total);
Unfortunately, on your sample data that will produce "0" for all 12 weeks.
So, I knocked up 1 million rows of randomised sample data: -
--== CREATE SAMPLE DATA ==--
IF OBJECT_ID('tempdb..#WebTV_Promo_Codes', 'U') IS NOT NULL
BEGIN
DROP TABLE #WebTV_Promo_Codes;
END
--== FILL SAMPLE DATA WITH 1 MILLION ROWS OF RANDOM DATA ==--
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS ID_CAT,
(ABS(CHECKSUM(NEWID())) % 10) + 1 AS ID_DEPT,
REPLACE(CAST(NEWID() AS VARCHAR(36)),'-','') AS CODE,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('2010' AS DATETIME) /*(Start date, e.g. '2010-01-01 00:00:00*/ AS INSERTDATE,
'felicia' AS ADDED_BY,
CASE WHEN (ABS(CHECKSUM(NEWID())) % 2) + 1 = 2 THEN 1 ELSE 0 END AS ACTIVE
INTO #WebTV_Promo_Codes
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
The data looks like this: -
ID ID_CAT ID_DEPT CODE INSERTDATE ADDED_BY ACTIVE
----------- ----------- ----------- ---------------------------------- ----------------------- -------- -----------
1 55 2 F8569FB8B15E423BBA071A694180CB95 2041-01-11 06:15:17.300 felicia 0
Which is similar enough to your sample data for our purposes, I think.
I then deleted all of the data for a particular week: -
DELETE FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN '2012-10-07 00:00:00.000' AND '2012-10-14 00:00:00.000';
And ran my code: -
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, b.Total
FROM TALLY a
OUTER APPLY (SELECT COUNT(1)
FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN a.X AND a.N) b(Total);
On this run, it produced the following (remember the sample data is randomised, so if you run it yourself it'll be different): -
DateRange Total
----------------------- -----------
2012-10-21 00:00:00.000 230
2012-10-14 00:00:00.000 0
2012-10-07 00:00:00.000 231
2012-09-30 00:00:00.000 240
2012-09-23 00:00:00.000 241
2012-09-16 00:00:00.000 230
2012-09-09 00:00:00.000 250
2012-09-02 00:00:00.000 232
2012-08-26 00:00:00.000 233
2012-08-19 00:00:00.000 207
2012-08-12 00:00:00.000 237
2012-08-05 00:00:00.000 212
Hope that answers everything, post back if you have any queries about what I did.