October 26, 2012 at 5:19 am
Sorry, only just spotted your reply!
Gohloum (10/25/2012)
Thanks for those links! I have read the one on Tally and I completely understand the concept, especially from a performance issue. Very good stuff to know and I'm now aware of this as a solution. I'll read the article on APPLY next, although I did read one somewhere else when I first saw your code. One of my pet peeves is to actually know and comprehend code one uses, especially when help has been given online, which brings me to a few questions:
I feel the same, after all I won't be the one having to support the code that I posted. With that in mind, let's try and clear up your questions 😀
Gohloum (10/25/2012)
1 - I understand the top part as far as the CTE goes for creating the long table, however I am a bit fuzzy on the 'N', a, and b, and the roles they play up here. Are a and b different values than later when they are used as table aliases?
This is mainly my fault, I use very generic table alias' when I post solutions online because there's noway for me to know how you'd prefer them.
If we grab one of the CTEs and have a look: -
;
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1)
SELECT a.N, b.N
FROM CTE a
CROSS JOIN CTE b;
Basically, we've created a cartesian product of the derived table "CTE". Because we are CROSS JOINing the CTE derived table, we have to alias it otherwise SQL Server can't tell the difference between the first instance of the derived table and the second instance. I suppose we could rewrite the alias to show what it actually is, like so: -
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1(N) AS (SELECT 1 FROM CTE cteInstance1 CROSS JOIN CTE cteInstance2),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2(N) AS (SELECT 1 FROM CTE1 cte1Instance1 CROSS JOIN CTE1 cte1Instance2),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3(N) AS (SELECT 1 FROM CTE2 cte2Instance1 CROSS JOIN CTE2 cte2Instance2),
--== 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 cte3Instance1 CROSS JOIN CTE3 cte3Instance2),
Gohloum (10/25/2012)
2 - In my normal coding day, I am more use to explicitly defined variables where it appears SQL allows more of an implied concept... In other words, N is not DECLARED as something. Can you shed a little light on this and I assume (N) and N are the same memory location within the entire context of the statement?
Again, probably my fault. I guess what you're talking about it this part: -
WITH CTE(N)
Basically, this simply says that my derived table called CTE has 1 column that is called "N". Again, we can rewrite this if you prefer: -
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE AS (SELECT 1 AS CTE_N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1 AS (SELECT 1 AS CTE1_N FROM CTE cteInstance1 CROSS JOIN CTE cteInstance2),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2 AS (SELECT 1 AS CTE2_N FROM CTE1 cte1Instance1 CROSS JOIN CTE1 cte1Instance2),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3 AS (SELECT 1 AS CTE3_N FROM CTE2 cte2Instance1 CROSS JOIN CTE2 cte2Instance2),
--== THIS PART OF THE CTE (CTE4) BUILDS A DERRIVED TABLE WITH 10,000,000,000,000,000 ROWS
CTE4 AS (SELECT 1 AS CTE4_N FROM CTE3 cte3Instance1 CROSS JOIN CTE3 cte3Instance2),
CTE5 AS (--== THIS PROVIDES A 0 BASE FOR THE DATE CALCULATION
SELECT 0 AS CTE5_N 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) CTE4_N
FROM CTE4)a
),
TALLY AS (SELECT
--== THIS DATE CALCULATION LOOKS FOR LAST SUNDAY, THEN TAKES THE NUMBER
--== FROM CTE5 (-1 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*-(CTE5_N-1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)) AS TALLY_N,
--== THIS IS THE SAME, BUT 1 WEEK REMOVED
DATEADD(dd, 7*-CTE5_N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)) AS TALLY_X
FROM CTE5)
Personally, I think it's cleaner to define them as part of the start of the CTE definition.
Gohloum (10/25/2012)
3 - I do understand aliases, and thus I see a is TALLY and b and c are my tables. So I am assuming that a.X is the X in TALLY (N, X). However what is interesting is TALLY(N, X) with an AS (SELECT...) really threw me at first. But now under closer examination, am I correct in assuming that TALLY is 2 columns with the 2 selected dates from the SELECT statement on the right side of the AS filling column 1 N and column 2 X and the row count is = to the row count of CTE5? If so then that's pretty darn slick!
That's correct 🙂
Gohloum (10/25/2012)
4 - d.Total. I see d(Total) in my where clause but no other ref to 'd'. So I'm not quite sure how d made it to the SQL party. Need a little demystifying here.
"d" is the table alias for this subquery: -
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
The brackets followed by "Total" just defines the name of the column in the derived table. You could rewrite it like this: -
SELECT a.X AS WeekBeginDate, d.Total
FROM TALLY a
OUTER APPLY (--== THIS IS THE TOTAL NUMBER OF CODES PER WEEK
SELECT COUNT(1) AS Total
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
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply