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!
Check Out Our SQL Blog - blog.pragmaticworks.com/topic/...
👍 If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use the code "youtube20" at check out when purchasing our On-Demand Learning classes to get an extra 20% off - pragmaticworks.com/pricing/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning Courses FREE Trial: learning.pragmaticworkstraini...
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/
Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️CZcams: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
Email: [e-mail]

[@alexpajp123]

This guy is a talented teacher.

[@JustBiTurbo]

Thanks Mitchell! Well explained

[@AvagyanAvag]

Very helpful intro, thanks a lot Mitchell!

[@bisratgezahgne914]

Thanks , Easy to Understand

[@cormackeenan8175]

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?

[@carrotcarrot3237]

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

[@timothymbogo5849]

Thanks Good tutorial

[@marklandgraf7667]

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

[@kevintownsley8500]

@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?

[@tbowyer8210]

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

[@lunarmodule6419]

Thx

[@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!

[@vasilybulochkin4622]

1. hold alt
2. shift+alt+down arrow OR shift+alt+mouse click to add cursors, then you just type in what you need and it will appear on all cursors. to get back to one cursor press Esc

[@darshanparulekar778]

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

[@PragmaticWorks]

Hi Darshan, you don't need to watch any pre videos before this one.

[@drakegao816]

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?

[@goodcbd6039]

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

[@lordybahi2781]

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

[@bilelkaaniche2204]

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

[@akirablac]

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

[@junaidmalik9593]

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

[@PragmaticWorks]

It is! Thanks Junaid, thanks for the kind comments. -Mitchell Pearson

[@manojtalwar7407]

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

[@Astus314]

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. xxxxx994.838, xxxxx658.438, xxxxx793.798.
It's jumbled the order. Sorry if this is covered later in the video. :)

[@houstonbova3136]

It looks like the functionality sorts on column order from left to right. If you notice the values are flipped and the order values for the second column PurchaseOrderDetailID is also flipped. I have a feeling if you added this column as a secondary ORDER BY this error would be visually corrected. It seems like the partition by was calculated under this assumption but, it wasn't displayed out with this assumption.

[@Pooja-pd1ee]

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.

[@sumankhanal5821]

He didn't wanna engage in risky business which revolves around probability especially after a responsible step like marriage.

[@swetawalker8390]

hello do you give private lesson

[@desktopdatacrunching4032]

We do have a virtual mentoring service here: pragmaticworks.com/virtual-mentoring/

[@freakzisback]

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

[@isaacmihaeli3261]

Sorry sound brakes off.

[@PragmaticWorks]

Thanks for the feedback.

[@ulysses1904]

Please stop saying "kind of".