Get power lines to maintain

In the provided GridStructure table, the fields that describe the connection between lines (EquipmentID,EquipmentID_To,EquipmentID_From) and the characteristics of the lines (e.g. Description, ConditionAssessment, VoltageLevel) are already defined.

Now, your task is to find the connected lines of the line with EquipmentID = 3 that have bad or repair as ConditionAssessment and have a VoltageLevel equal to HV. By doing this, you can answer the following question:

Which lines have to be replaced or repaired according to their description and their current condition?

You have to create a CTE to find the connected lines and finally, to filter on the desired characteristics.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

Define the CTE maintenance_List.
Start the evaluation for line 3.
Join GridStructure with maintenance_List on the corresponding endpoints.
Use LIKE to filter the power lines with ConditionAssessment of either exchange or repair, and a VoltageLevel of HV.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Define the table CTE
___ ___ (Line, Destination, Source, Description, ConditionAssessment, VoltageLevel) ___ (
	SELECT 
  		EquipmentID,
  		EquipmentID_To,
  		EquipmentID_From,
  		Description,
  		ConditionAssessment,
  		VoltageLevel
  	FROM GridStructure
 	-- Start the evaluation for line 3
	___ EquipmentID = ___
	UNION ALL
	SELECT 
		Child.EquipmentID, 
  		Child.EquipmentID_To,
  		Child.EquipmentID_FROM,
  		Child.Description,
  		Child.ConditionAssessment,
  		Child.VoltageLevel
	FROM GridStructure Child
  		-- Join GridStructure with CTE on the corresponding endpoints
  		___ maintenance_List 
    	ON maintenance_List.Line = Child.___)
SELECT Line, Description, ConditionAssessment 
FROM maintenance_List
-- Filter the lines based on ConditionAssessment and VoltageLevel
WHERE 
    (ConditionAssessment ___ '%exchange%' ___ ConditionAssessment ___ '%repair%') ___ 
     VoltageLevel LIKE '%HV%'

Edit and Run Code