1. I think that @Year could be TINYINT, just like @Quarter. Minor quibble, of course. 2. At first viewing, it is somewhat jarring to see you make errors., then have to go back and correct them later. But then we get to view the debugging process, which is also valuable. Thank you for posting your content. It is helpful. Keep it up, Brad!
13:34 Is there a business reason or a technical reason for not writing the calculation as "(Total_Pay * 0.05) AS Bonus"? It obtains the same result that your version obtains. Your version is probably clearer, and easier to follow, which is probably the point. TIA.
It works, nested CTEs actually work: WITH CTE1 AS ( SELECT CAST(AVG([Sales])as int) as company_avg FROM Orders ) , CTE2 AS ( -- Average sales by customer SELECT [Customer ID], CAST(AVG(Sales) as int) as customer_avg FROM Orders GROUP BY [Customer ID] ) -- Which customer average sales is equal company average sales SELECT [Customer ID], customer_avg, company_avg FROM CTE1, CTE2 WHERE customer_avg = company_avg;
Hi, thanks for the comment, unfortunately I no longer have the data but you can try with a database like AdventureWorks and I will give assistance if you need it. I make sure my latest videos include code to allow viewers to follow along.
1. I think that @Year could be TINYINT, just like @Quarter. Minor quibble, of course.
2. At first viewing, it is somewhat jarring to see you make errors., then have to go back and correct them later. But then we get to view the debugging process, which is also valuable.
Thank you for posting your content. It is helpful. Keep it up, Brad!
Could use SMALLINT but not TINYINT, range is only up to 255.
@@BeardedDevData Right. Thank you. (Formerly bearded dude.)
13:34 Is there a business reason or a technical reason for not writing the calculation as
"(Total_Pay * 0.05) AS Bonus"? It obtains the same result that your version obtains. Your version is probably clearer, and easier to follow, which is probably the point. TIA.
No, your way is perfectly fine, it's just one of those things when you look back and think, it would be less code to just to do it that way.
Please, make a video on nested queries (subqueries) and co-related queries and recursive queries
YOU ARE THE BEST SIR... i wish i have a member like you in my team...great clarity in concepts..
idk why you stopped making videos!!!
Thanks so much, I will actually be working on some videos this weekend that should be up next week.
It works, nested CTEs actually work:
WITH CTE1 AS
(
SELECT
CAST(AVG([Sales])as int) as company_avg
FROM Orders
)
, CTE2 AS
(
-- Average sales by customer
SELECT
[Customer ID],
CAST(AVG(Sales) as int) as customer_avg
FROM Orders
GROUP BY [Customer ID]
)
-- Which customer average sales is equal company average sales
SELECT
[Customer ID],
customer_avg,
company_avg
FROM CTE1, CTE2
WHERE customer_avg = company_avg;
Did I mention that they didn't or are you just confirming that you are using them? Your comment has just thrown me a little 🤔
@@BeardedDevData I am just confirming that you are using them. :)
Fantastic
HI Dev..
Your videos are very informative.But there's only one problem I am facing to follow you along that is how to get this CTE dataset.
Hi, thanks for the comment, unfortunately I no longer have the data but you can try with a database like AdventureWorks and I will give assistance if you need it. I make sure my latest videos include code to allow viewers to follow along.
Brad: We're still looking for CTEs Part 3. Recursive CTEs?
TIA. 👍
That's a great idea, unfortunately other commitments are taking my time at the moment but I hope to be back soon.