CTE Recursiva - Uma consulta elegante

    Pessoal, muito obrigado pelo retorno do post anterior, no total foram duas centenas de pageviews,cinco emails, três comentarios, um telefonema e um visitante croata(sim é verdade!).
    Motivado por esse retorno sigo com a continuação:
  CTE com Recursividade!!!
  
   Veja o caso “CTE no Busão”:
      
       Imagine uma determinada linha de onibus que realiza 10 paradas entre seu trajeto de Guarulhos a Campinas. O onibus possui um sistema que registra a data/hora de cada parada e qual foi a distancia percorrida entre uma parada e outra. Scrip Exemplo

-- Estrutura

CREATE TABLE Paradas (

             ParadaID int identity (1,1) PRIMARY KEY, -- PK
             DataParada datetime, -- Data/hora da parada
             Distancia int, -- distancia percorrida da ultima parada até agora
             ParadaAnteriorID int -- id da parada anterior
             )
-- Populando
INSERT INTO Paradas (DataParada,Distancia)
VALUES (GETDATE(),0)
GO

INSERT INTO Paradas (DataParada,Distancia,ParadaAnteriorID)
SELECT TOP 1
       DATEADD(MINUTE ,(RAND() * 100)+ 5,DataParada) ,
       CAST(RAND() * 33 as Int )+ 5 ,
       @@IDENTITY
FROM
       Paradas
ORDER BY DataParada DESC
GO 10

Dando um SELECT simples temos o seguinte resultado

ParadaID    DataParada              Distancia   ParadaAnteriorID

----------- ----------------------- ----------- ----------------
1           2011-06-07 17:23:59.543 0           NULL
2           2011-06-07 18:10:59.543 36          1
3           2011-06-07 19:31:59.543 30          2
4           2011-06-07 20:17:59.543 5           3
5           2011-06-07 21:37:59.543 27          4
6           2011-06-07 23:04:59.543 21          5
7           2011-06-07 23:19:59.543 37          6
8           2011-06-08 00:31:59.543 21          7
9           2011-06-08 01:13:59.543 6           8
10          2011-06-08 02:18:59.543 23          9
11          2011-06-08 02:37:59.543 28          10

--------------------------------------------------------------------

    Eis então que chega o Gerente da companhia de Viação e te pede um relatório que exiba cada parada e quantos KMs foram percorridos do inicio da viagem até aquele momento. Ai que entra o CTE.

    Uma CTE recursiva começa com a chamada Ancora. O registro Ancora é o fio por onde voce puxa a recursão. No nosso caso começamos com o registro inicial da viagem.

-- Ancora
SELECT
       DataParada,
       Distancia,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL

    Na sequência via UNION ALL montamos uma query semelhante fazendo um Join com a própria CTE , veja abaixo

WITH CTE_Viagem (DataParada,KMRodados,ParadaID)

AS (
-- Ancora
SELECT
       DataParada,
       Distancia,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL
UNION ALL   
-- Recursão
SELECT
       P.DataParada,
       P.Distancia + C.KMRodados,
/* Pulo do Gato! Acima é que se soma a distancia atual com a distancia da anterior */
       P.ParadaID
FROM
       Paradas P
INNER JOIN CTE_Viagem C  -- NOTE AQUI A CTE!!!
             ON P.ParadaAnteriorID = C.ParadaID
       )

SELECT
       DataParada,KMRodados,ParadaID
FROM
       CTE_Viagem

  

   E olha que maravilha de resultado!!! E como é elegante.

DataParada              KMRodados   ParadaID
----------------------- ----------- -----------
2011-06-07 17:23:59.543 0           1
2011-06-07 18:10:59.543 36          2
2011-06-07 19:31:59.543 66          3
2011-06-07 20:17:59.543 71          4
2011-06-07 21:37:59.543 98          5
2011-06-07 23:04:59.543 119         6
2011-06-07 23:19:59.543 156         7
2011-06-08 00:31:59.543 177         8
2011-06-08 01:13:59.543 183         9
2011-06-08 02:18:59.543 206         10
2011-06-08 02:37:59.543 234         11

     Não contente (e preguiçoso em fazer contas) o mesmo gerente te pede para colocar no relatório a quantidade em minutos que a viagem demorou até aquele momento. E voce simplesmente adiciona o seguinte:

WITH CTE_Viagem (DataParada,KMRodados,TempoViagem,ParadaID)

AS
(
-- Ancora
SELECT
       DataParada,
       Distancia,
       0 AS TempoViagem,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL
UNION ALL   
-- Recursão
SELECT
       P.DataParada,
       P.Distancia + C.KMRodados,
       C.TempoViagem + DATEDIFF(minute,C.DataParada,P.DataParada),--Pulo do Gato!
       P.ParadaID
FROM
       Paradas P
INNER JOIN CTE_Viagem C  ON P.ParadaAnteriorID = C.ParadaID
       )
SELECT
       DataParada,KMRodados,TempoViagem,ParadaID
FROM
           CTE_Viagem

E resultado:

DataParada              KMRodados   TempoViagem ParadaID

----------------------- ----------- ----------- -----------
2011-06-07 17:23:59.543 0           0           1
2011-06-07 18:10:59.543 36          47          2
2011-06-07 19:31:59.543 66          128         3
2011-06-07 20:17:59.543 71          174         4
2011-06-07 21:37:59.543 98          254         5
2011-06-07 23:04:59.543 119         341         6
2011-06-07 23:19:59.543 156         356         7
2011-06-08 00:31:59.543 177         428         8
2011-06-08 01:13:59.543 183         470         9
2011-06-08 02:18:59.543 206         535         10
2011-06-08 02:37:59.543 234         554         11
 

Demais!

Explicações técnicas, detalhadas e sem emoção a seguir no nosso amigo books online

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Abraços!!!