SQL Server Doubt 2012 - Query

Question

I am having a hard time. In the consultation below, you are bringing in this way.

Tarefa_Inclusao      Tarefa
167909               167920

SELECT TarIDInclusao [Tarefa_Inclusao],tarid [Tarefa] FROM Tarefa WHERE TarID = 167920. 

This consultation is another task. What I want is the following: I want that in the above query, perform a manipulation so that the field tarefa_inclusao instead of coming 167909, come equal 167907 of the below query.

Tarefa_Inclusao      Tarefa
167907               167909

SELECT TarIDInclusao [Tarefa_Inclusao],tarid [Tarefa] FROM Tarefa WHERE TarID = 167909

Answer 1

Use a recursive search to find the primordial record for the desired task:

WITH historico AS (
  SELECT t.taridinclusao AS tarefa_inclusao,
         t.tarid AS tarefa,
         1 AS nivel
    FROM tarefa t
   WHERE t.tarid = 167920
   UNION ALL
  SELECT t.taridinclusao AS tarefa_inclusao,
         h.tarid AS tarefa, -- Pega do historico para garantir que o primeiro valor será mostrado
         h.nivel + 1 AS nivel
    FROM historico h
         INNER JOIN tarefa t ON t.tarid = h.taridinclusao
   WHERE h.taridinclusao IS NOT NULL
)
SELECT TOP(1) h.*
  FROM historico h
 ORDER BY h.nivel DESC
OPTION(MAXRECURSION 0);

The first SELECT within the WITH will define the anchorage of the records, ie the record from which we start, in this case the task from which we have the number. After that, we will always reference the historico to verify the record taridinclusao concerning him.

WITH

A CTE (common table expression) provides the significant advantage of being able to self-reference, thus creating a recursive CTE. A recursive CTE is one in which an initial CTE is run repeatedly to return subsets of data until the complete result is obtained.