Gohloum (10/24/2012)
Oof... Just saw your reply. That went just a hair over my head. Would you mind adding a few comments in the code as the sections process to help me wrap my head around it? I think once I understand how your code is actually processing to product the results, then I am golden.For instance, I do a lot of loops and switch/case statements in game code, so it was fairly easy to build my Dynamic statement because I understood how loops and switch/case work.
Any light on the topic would be most appreciated (and worth another beer for sure!):cool:
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
--== THIS PART OF THE CTE (CTE4) BUILDS A DERRIVED TABLE WITH 10,000,000,000,000,000 ROWS
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (--== THIS PROVIDES A 0 BASE FOR THE DATE CALCULATION
SELECT 0 UNION ALL
--== THIS CREATES NUMBERS FROM 1 TO THE NUMBER OF WEEKS REQUIRED
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT
--== THIS DATE CALCULATION LOOKS FOR LAST SUNDAY, THEN TAKES THE NUMBER
--== FROM CTE5 (0 TO THE NUMBER OF WEEKS REQUIRED-1) AND REMOVES 7 DAYS FOR EACH
--== ONE. THIS PROVIDES US WITH THE WEEKS FOR THE DATE RANGE
DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
--== THIS IS THE SAME, BUT 1 WEEK REMOVED
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 (--== THIS IS THE TOTAL NUMBER OF CODES PER WEEK
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
--== THIS LIMITS THE ROWS RETURNED TO ONLY THOSE INCLUDED IN OUR DATE
--== RANGE CALCULATIONS
AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);