title: Can my recursive query be quicker?
body: <p>I've written the below recursive query to bring back a chain of leads together starting from the leaf or initial lead and returning all the previous leads.</p>
  
  <p>The end result will be fed into another query that produces one row per chain, i'm not keen on recursive queries in general but i dont see another way to write this.</p>
  
  <pre><code>WITH [D1] AS
  (
      SELECT 
          [Opp]._Customer_ID [CusID], 
          [Opp].Opportunity_ID [OppID], 
          [Opp].Reference [Ref], 
          [Opp].CoverStartDate_Actual [CoverStart], 
          [Opp].Date_Cancelled [Cancelled], 
          [Opp].Renewal, 
          [Opp]._Product_ID [ProID], 
          [Opp].inf_Opportunity_ID_Prior [PriorOppID], 
          [Opp].Policy_Number [PolicyNo], 
          [Opp].RealOpp [Real], 
          [Opp].Deleted, 
          [Opp].[Status],
          [Prior].Reference as PriorRef, 
          DATEDIFF(MONTH,[Prior].CoverStartDate_Actual,[Opp].CoverStartDate_Actual) as PriorOppMonthsApart, 
          [Prior].Months_Cover as PriorMonthsCover
      FROM 
          dbo.crm_Opportunity [Opp]
              LEFT OUTER JOIN dbo.crm_Opportunity [Prior] ON [Prior].Opportunity_ID = [Opp].inf_Opportunity_ID_Prior
      WHERE 
          ([Opp].Deleted = 0) 
          AND ([Opp].[Status] = 5) 
          AND ([Opp].RealOpp = 1)
  ),
  </code></pre>
  
  <p>This is the initial query to return all the data that we will perform operations on. I could add a TempTable here or Table Varible, to hold this info.</p>
  
  <pre><code>[D2] AS
  (
      SELECT 
              [D1].OppID, 
              [D1].Ref, 
              [D1].CoverStart, 
              [D1].Cancelled, 
              [D1].Renewal, 
              [D1].ProID, 
              [D1].PriorOppID, 
              [D1].PolicyNo,
              1 AS Depth, 
              [D1].Ref AS TopRef, 
              CONVERT(VARCHAR(7),NULL) AS NextRef, 
              [D1].PriorRef, 
              [D1].PriorOppMonthsApart, 
              [D1].PriorMonthsCover, 
              [Cu].Introducer_Company_ID [Introducer], 
              [D1].OppID AS _Opportunity_ID_Top
  
      FROM 
              [D1] 
                  INNER JOIN dbo.crm_Customer [Cu] ON [D1].CusID = [Cu].Customer_ID
  
      WHERE 
              ([Cu].Deleted = 0) 
              AND ([Cu]._Company_ID = 'bd164825-a16a-4bee-9b01-6971604dda63')
              AND 
                  (
                  SELECT 
                      COUNT(*) 
                  FROM 
                      ACTIVEQUOTE.dbo.crm_Opportunity op2 
                  WHERE 
                      ([D1].[PriorOppID] = [D1].OppID)
                      AND ([D1].Deleted = 0) 
                      AND ([D1].[Status] IN (2,3,4,5))
                  ) = 0
  
      UNION ALL
  
      SELECT 
              [D1].OppID, 
              [D1].[Ref], 
              [D1].[CoverStart],
              [D1].[Cancelled], 
              [D1].Renewal, 
              [D1].[ProID], 
              [D1].PriorOppID, 
              [D1].PolicyNo,
              [D2].Depth + 1 as Depth, 
              [D2].TopRef, 
              CONVERT(VARCHAR(7),[D2].Ref) as NextRef, 
              [D1].PriorRef, 
              [D1].PriorOppMonthsApart, 
              [D1].PriorMonthsCover, 
              [D2].[Introducer], 
              [D2]._Opportunity_ID_Top
  
      FROM 
          [D1] 
              INNER JOIN [D2] ON [D1].OppID = [D2].PriorOppID
  ),
  </code></pre>
  
  <p>Welcome to recursive hell, as i said i'm not a fan of recursive queries but it was the only way i could use to feed back up the chain per say. The query takes the prior lead reference and returns it until it comes to leads with a NULL prior lead.</p>
  
  <pre><code>[D3] AS
  (
  SELECT  
          [D2].[ProID], 
          COUNT(*) OVER (PARTITION BY TopRef) AS Years, 
          [D2].Depth,
          [D2].TopRef, 
          [D2].Ref, 
          [D2].NextRef, 
          [D2].PriorRef, 
          CASE WHEN [D2].[PriorRef] IS NULL THEN 1 ELSE 0 END AS OriginOpp,
          [D2].Renewal,
          [D2].OppID,  
          [D2].CoverStart, 
          [D2].Cancelled,  
          [D2].PolicyNo, 
          [D2].PriorOppMonthsApart, 
          [D2].PriorMonthsCover,
          [D2].Introducer,
          [D2]._Opportunity_ID_Top
  FROM 
          [D2]
  )
  
  SELECT 
      *
  FROM 
      [D3]
  </code></pre>
  
  <p>The final piece of the puzzle is to format the data into usable rows, the PARTITION is used to determine how old the sale is.</p>
  
  <p>Then i select everything.</p>