Introductions
Recursive CTEs are a way to reference a query over and over again, until the deemed end point or the termination check, as it is called. It’s worth noting that the example shown in this blog will be done on Azure SQL Database.
There is a certain structure with the use of recursive CTEs, which is as shown below:
Figure 1: Recursive CTE structure
There is an initial query which is known as the ‘anchor query’ which in the case of the image shown above, is selecting the number ‘1’ as ‘n’. There is a secondary query which is joined together with the use of a ‘UNION ALL’. The secondary query, also known as ‘Recursive Member’ (shown in the image above) is the query which runs continuously until the recursive member has hit the termination point (also shown on the image above).
Scenario
An individual is working in HR and wants to find out which individual is managing who. This may be for several reasons such as, they need to ask the managers on the progress of their staff and if their appraisal is coming up or is due.
Another scenario may be that the company is enrolling more staff and wants to find out the capacity of the current staff or find individuals who have not yet got anyone to manage to give them the opportunity to do so.
Solution
As mentioned above, the examples on this blog as completed on Azure SQL database. So, let’s get into the solution. This demo which I have prepared for this blog is widely used to showcase the use case of Recursive CTEs.
Figure 2: Dataset
This above is the dataset that we are going to be working with and the dataset that we will use in order to build our recursive CTE. So, in this example, we can see that there are 2 records which does not have a value populated for the column ‘ReportsTo’, which we will assume that they are the top managers.
To build our recursive CTE, we are going to start off with by selecting the records where the ‘ReportsTo’ equals NULL.
SELECT employeeId, FirstName, LastName, ReportsTo FROM Sales.Staff WHERE reportsTo IS NULL
This shown above will be the anchor member to the Recursive CTE as shown in Figure 1. This will be the initial query that will set the foundation for the recursive member. Between the anchor query and recursive, the ‘union all’ join is used, like shown below:
UNION ALL
Next will be the recursive member which will run continuously until the termination check has been reached. Now let’s create the next 2 steps which includes the recursive member as well as the termination check which is seen in the inner join of the query.
SELECT s.employeeId, s.FirstName, s.LastName, s.ReportsTo FROM Sales.Staff s INNER JOIN StaffHierarchy sh ON sh.employeeId = s.reportsTo
To check the final codebase, see below once the final touches has been completed. As you can see, the codes that were gone over above has been wrapped inside a ‘WITH StaffHierarchy AS’ which invokes the recursive CTE, or CTE for that matter.
WITH StaffHierarchy AS( SELECT EmployeeId, FirstName, LastName, ReportsTo FROM Sales.Staff WHERE ReportsTo IS NULL UNION ALL SELECT s.employeeId, s.FirstName, s.LastName, s.ReportsTo FROM Sales.Staff s INNER JOIN StaffHierarchy sh ON sh.employeeId = s.reportsTo ) SELECT employeeId, FirstName, LastName, ReportsTo FROM StaffHierarchy ep
And finally, the code is finished off with a select from the CTE itself, to showcase the results of the recursive CTE.
If you enjoyed this blog, check out our full blog list here.
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb