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.
Meet the Team – Matt How, Principal Consultant
Next up in our series of meet the team blogs is Matt How. Matt has
Apr
MLFlow: Introduction to MLFlow Tracking
MLFlow is an open-source MLOps platform designed by Databricks to enable organisations to easily manage
Apr
Adatis are pleased to announce expansion plans into India
Adatis has offices in London, Surrey and Bulgaria – and has some big expansion plans
Mar
Querying and Importing Data from Excel to SSMS
Introduction There are couple of ways to import data from Excel to SSMS – via
Mar
Data Engineering for Graduates and New Starters
This blog aims to give potential graduates and other new starters in the industry some
Mar
Passing DP-900 Azure Data Fundamentals
Back in December, I took the DP-900 Azure Data Fundamentals exam which is one of
Feb
Real-time Dashboards Provide Transparency to Everyone
Real-time dashboards enable data analytics firm Adatis to be agile and transparent with its team
Feb
Data is creating a new age of business agility
Developing a data strategy is central to ensuring your business can respond to disruptions and
Feb