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.
Building Data Apps with Python’s Streamlit
Off-the-shelf solutions for interactive data app development such as Microsoft Power BI are great –
Nov
Power BI to Power AI – Part 3
This post is the third part of a blog series on the AI features of
Nov
Data Factory in Fabric
As data engineers, we use Azure Data Factory on a daily basis to collect datasets
Nov
Microsoft Fabric Is Generally Available
Microsoft Fabric is now Generally Available What has happened? On the 15th November Microsoft announced
Nov
An Introduction to Semantic Link in Microsoft Fabric
Having previously worked in the water & energy industries, I recently created a utilities PowerBI
Nov
Prompt Engineering and Creating a Python Function to Query OpenAI
Artificial intelligence tools, like Chat GPT, are all the buzz right now, and rightfully so
Nov
Exploring different options for collaborative workspaces: Hex, Hyperquery & Deepnote
Real-time collaboration, code completion and data versioning are just a few of the latest capabilities
Nov
NHS Blood and Transplant Shortlisted for Government Project Delivery Awards
We are delighted to share that NHS Blood and Transplant’s data platform project has been
Nov