Скачать или смотреть Advanced SQL Tutorial | Looping in SQL Server | WHILE Loops with Dynamic SQL

Advanced SQL Tutorial | Looping in SQL Server | WHILE Loops

In this training video, we walk through how to write a while loop in SQL in order to loop through a set of a columns that we want to breakout lead conversation rate by without copying and pasting code!

Partner with us as a channel member to get access to exclusive perks:    / @valuedrivenanalytics  

If you'd like to learn more about SQL data manipulation, you can check out our intro to SQL training here, which covers selecting data from a data table, creating new columns, joining tables together, and aggregating and grouping data:    • SQL Tutorial for Beginners | Stock Market ...  

Learn more about joins and unions in SQL here:    • Join and Union Tables in SQL and Pandas  

Learn more about subqueries and CTEs in SQL here:    • Mastering Advanced SQL Techniques | Subque...  

Learn more about the rank statement in SQL here:    • Intermediate SQL | Using Rank and Partitio...  

Learn more about the having clause in SQL here:    • Advanced SQL Tutorial | Having Clause  

Learn more about the coalesce function in SQL here:    • Coalesce Function in SQL Server: An Interm...  

Learn more about date functions in SQL Server here:    • Intermediate SQL Tutorial | Date Functions...  

Learn more about the lag and lead functions in SQL Server here:    • Advanced SQL Tutorial | Lag and Lead Windo...  

Learn more about slowly changing dimension tables in SQL here:    • Advanced SQL | Slowly Changing Dimensions ...  

Here is the final query written for reference:

DECLARE @fields nvarchar(100)
DECLARE @field nvarchar(100)
DECLARE @comma int
DECLARE @query nvarchar(1000)

/*Update list with comma-separated list of variables to loop through*/
SET @fields = 'State,Marketing Channel,Lead Submission Month' + ','

SET @comma = CHARINDEX(',',@fields)

WHILE @comma (ENTER GREATER THAN SYMBOL HERE SINCE YOUTUBE DOESN"T ALLOW IT IN DESCRIPTIONS) 0
BEGIN
SET @field = left(@fields,@comma-1)
PRINT 'Variable = ' + @field

SET @query = '
Select
[' + @field + '],
count(*) as [# Leads],
1.0000*sum(Converted)/count(*) as [% Converted]
from VALUE_DRIVEN_ANALYTICS.LOOP_ILLUSTRATION.LEADS
group by [' + @field + ']
order by 1.0000*sum(Converted)/count(*) desc
'
/*PRINT 'Query = ' + @query*/
EXEC sp_executesql @query

SET @fields = STUFF(@fields,1,@comma,'')
SET @comma = CHARINDEX(',',@fields)
END

______________________________________________

CHAPTERS:
0:00 WHILE loop in sql server
0:18 Benefits of using loops
5:50 Avoiding rounding errors in SQL division
9:30 How to declare a variable in SQL
12:30 How to set the value of a SQL variable
14:00 How does charindex work in SQL
15:10 How does a WHILE loop work in SQL?
17:00 What does the LEFT function do in SQL?
18:09 How to print text in SQL
19:15 What does the STUFF function do in SQL?
23:45 How to write a dynamic SQL query
26:10 How to use the EXEC command in SQL
28:00 How to use sp_executesql in SQL

______________________________________________

ADDITIONAL VIDEOS YOU MAY LIKE:

Please subscribe to get updates on the latest analytics training videos available!
Whether you’re looking for training on technical skills or soft skills, whether you’re an aspiring analyst or an analytics executive, we have the perfect analytics training video for you! Check out our free training videos at https://valuedrivenanalytics.com/comp...

______________________________________________

ABOUT VALUE DRIVEN ANALYTICS:

Value Driven Analytics is your source of rigorous, affordable, and fast analytics consulting and transformation. If you need an interactive dashboard, analysis, data science model build, automated data process, analytics or leadership training, analytics team management, analytics transformation, or 1-hour analytics consultation (troubleshooting, project advice, career advice), give us a call at 1-877-VAL-DRVN (1-877-825-3786) or learn more about our services at https://valuedrivenanalytics.com/serv...

Stay connected with us!
LinkedIn:   / value-driven-analytics  
Twitter:   / vda_consulting  
Facebook:   / 61550273999898  

If you need help troubleshooting code, project advice, or personalized career advice, consider booking a 1-hour analytics consultation with me. You'll be amazed by what we can accomplish in just 1 hour! https://valuedrivenanalytics.com/hour...

______________________________________________

KEYWORDS:

looping, sql looping, looping in sql, while loops, while loop, while loops in sql, sql while loops, sql while loop, while loop in sql, advanced sql, advanced sql tutorial, sql, data analyst, data scientist, data analytics, data science, analyst, analytics, data exploration, sql data exploration, automation, sql automation, charindex, dynamic SQL, left, stuff
#sql #dataanalyst #datanalytics #datascience #datascientist #sql