Sorry, didn't see the join in there so provided a part solution.

Try this instead: -

--== 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, d.Total

FROM TALLY a

OUTER APPLY (SELECT COUNT(1)

FROM #WebTV_Promo_Customer b

INNER JOIN #WebTV_Promo_Codes c ON b.ID_CODE = c.ID

WHERE c.ACTIVE = 0 AND c.ID_CAT <> 2

AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);

I modified that on the way to my car using my phone, so check the syntax before executing. I'll check for replies either tonight when I get home or tomorrow morning.