SQL Server - Recursive Conditional Calculation.

I would like to share one SQL techniques to perform calculations recursively on SQL tables.
This question is also very useful helpful in SQL Developer interview.

Interviewer asked me below question.

I have one table called CanalMaster. CanalMaster Contains all information related to Canal.

Below are some information about CanalMaster table.

There are two types of Canal

• Parent Canal - Doesn't have ParentCanalId
• Child Canal - have Canal Origin after Kilometer

below is the SQL table.

CREATE TABLE CanalMaster
(
CanalId            numeric , -- Canal Unique Id
CanalName          Varchar(100), -- Canal name
ParentCanalId      numeric, -- Canal Parent Id
CanalLength        numeric(10,2), -- Canal Length
ChildCanalOriginKM numeric(10,2) -- Child Canal start origin from parent after kilometer.
)  

-- Sample Records.
INSERT INTO CanalMaster VALUES (1,'AAA',NULL,1000,NULL)
INSERT INTO CanalMaster VALUES (2,'BBB',1,700,30)
INSERT INTO CanalMaster VALUES (3,'CCC',2,600,450)
INSERT INTO CanalMaster VALUES (4,'DDD',1,800,800)
INSERT INTO CanalMaster VALUES (5,'EEE',3,200,450)
INSERT INTO CanalMaster VALUES (6,'FFF',5,300,150)

Question: I want to calculate total length of canal "FFF" from origin.


Solution.
Below SQL Query is solution.

WITH CanalEx (CanalId, CanalName, CanalLength,ParentCanalId)AS
(
   SELECT 
          a.CanalId,
          a.CanalName,
          a.ChildCanalOriginKM CanalLength,
          a.ParentCanalId
   FROM
          CanalMaster A
   WHERE  CanalId = 6 -- Specified Canal Length

   UNION all
  
   SELECT 
          a.CanalId,
          a.CanalName,
          a.ChildCanalOriginKM CanalLength,
          a.ParentCanalId
    FROM
          CanalMaster A
          JOIN CanalEx B ON A.CanalId = B.ParentCanalId
)

select SUM(Inn.CanalLength)
FROM
(
    SELECT
          CanalId,
          CanalName,
          CanalLength,
          ParentCanalId
    FROM
          CanalEx
    WHERE
          CanalLength is not null -- To avoid NULL aggregation warning
    UNION
    SELECT
          a.CanalId,
          a.CanalName,
          a.CanalLength ,
          a.ParentCanalId
    FROM
          CanalMaster A
    WHERE
          CanalId = 6
) as Inn

To know more about SQL Server WITH clause please visit: http://msdn.microsoft.com/en-us/library/ms175972.aspx