Advanced T-SQL Takeover!

By now you know the basics of T-SQL, in this session, we will discuss how to solve T-SQL problems using more complex T-SQL concepts like CTEs, Window Functions, and newer less used functions!

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -

Next step on your journey:

Let's connect:

Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]

[AvagyanAvag]

Very helpful intro, thanks a lot Mitchell!

[marklandgraf]

A CTE can also be preceded with GO instead of a semicolon. GO might be a little bit more readable for some.

[darshanparulekar]

hey mitchell, do i have to see any pre videos before jumping to this

[Pestrutsi]

In SSMS how do you
1) Select boxed pieces of SQL code as you do at 1:09:50 as when I'd try to do that normally, I'd select every line fully, not just a boxed part of the code as you would select let's say a rectangular subset of a set of folders/programs in Windows OS
2) Add commas to multiple lines as you do in 1:10:00
Many thanks for any responses!

[cormackeenan]

I learnt a lot thanks, Q: how import is it to size tables so they fit cluster on the disc on a 64 bit system, or has SSD rendered this notion obsolete?

[carrotcarrot]

thank you so much sir. very helpful . God bless you

[junaidmalik]

Wow is this the same Mitchelle Pearson wgo teaches Azure on you tube? If yes tgen big fan

[tbowyer]

The sound breaks occasionally.
Gets down to business at 03:19

[manojtalwar]

Hello Mitchell, don't have access to AdventureWorks2016 database - to be able to see MicrosoftStockHistory table. Any idea pls?

[akirablac]

'MicrosoftStockHistory' table doesn't exist in AdventureWorks2016 database. Where should I get it?

[lordybahi]

Hello, How to get the DEMO's SCRIPTS of the webinar?

[VioletClaw]

it is weird that I tried rows between 'current row and unbounded following' and got the same result as 'current row and unbounded preceding'. Could that be because of my order by selection in the window function?

[kevintownsley]

@26:02 I'm trying out 'ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING' on my own dataset and I'm getting the exact same result as 'ROWS UNBOUNDED PRECEDING', essentially a running total instead of a countdown. Any idea what's causing this?

[Astus]

Got a bit confused at 26:20 because the 'ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING' doesn't give a true reverse running total. Look at the totaldue value for the top 3 rows.

It's jumbled the order. Sorry if this is covered later in the video. :)

[goodcbd]

Hello mitchell, great video. One question:
Why at 1:26:20 the SUM OVER PARTITION BY aggregates on the all rows (like and un-expected behavior?) instead of the default behavior which is until CURRENT_ROW.
Also, is the WHERE clause affects the Window/Frame range?
Thank you

[Pooja-pdee]

haha.. It literally cracked me up was when he said - "I used to do stocks but then I got married". Why would he say something like that? :P However, overall it was a great session on analytical functions.

[bilelkaaniche]

for the first business problem, we should use this query :
;with salesdetails as (
select
SalesOrderID, CustomerID, TotalDue,
row_number() over (partition by CustomerID order by SalesOrderID) as RN
from sales.SalesOrderHeader
)
select SO.CustomerID,
sum( case when RN<=3 then CTE.TotalDue
else 0 end ) as first3order, SO.SalesOrderID,
SO.TotalDue
from sales.SalesOrderHeader SO
inner join salesdetails CTE
on SO.CustomerID = CTE.CustomerID
group by SO.CustomerID, SO.SalesOrderID, SO.TotalDue
having sum( case when RN<=3 then CTE.TotalDue
else 0 end ) >=10000

Any idea ?

[freakzisback]

Derived tables are great if u need to use sql as a view. CTEs cannot be made to views.

[ulysses]

Please stop saying "kind of".