Using a Recursive CTE for Logic in VAE Protocol

Until this week, all of the recursive CTE posts I had read used an employee hierarchy example. Then my colleague pointed me to the best technical blog post I have ever read where Brad Schulz uses a sales running total example in his blog post entitled,  “This Article On Recursion Is Entitled “This Article On Recursion Is Entitled “This Article… ∞” ” Even if you are not interested in SQL you should check out this article for the great story, writing style, examples or recursion, and links to Wikipedia pages about things like fractals and mobius strips.

My blog post is about using a recursive CTE in SQL Server for part of the logic in the Center for Disease Control’s (CDC) Ventilator Associated Event (VAE) Protocol. 

Imagine you have a temp table called #vent_days where each row represents a patient and a day that they were on a mechanical ventilator. You have already identified which vent days meet the criteria for a VAE day of event except you haven’t yet taken into account the rule: when a VAE day of event occurs, another VAE day of event cannot occur for 14 days. You have filtered the temp table down to just those patient visits that have a VAE. Your temp table has the columns represented by the following SELECT statement.

 
SELECT
   patient_visit_id
   , vent_day_date      /*date of mechanical ventilation*/
   , vent_day           /*the number of days a patient is on a mechanical ventilator as of the vent_day_date*/
   , pos_VAE_doe_flg    /*possible VAE day of event flag. 1 indicates that this day meets the
                       criteria for a VAE day of event except the rule that a VAE day of event may not occur until 14 days after a previous VAE day of event*/
   , pos_VAE_doe_order /*only populated where pos_VAE_doe_flg is 1. The ordered number of possible VAE days of event per patient visit.*/ 
FROM
   #vent_days  

Below is how you can use a recursive CTE to complete the logic by fulfilling the criteria that a VAE day of event must not occur until 14 days have passed since a previous VAE day of event.

; 
WITH VAE_doe (patient_visit_id, vent_day_date, vent_day, pos_VAE_doe_order, prev_doe, doe_flg) 
AS (
   /*  the anchor query is the first possible VAE day of event for each patient visit. doe_flag is set to 1 because it will always be an actual day of event.*/
   SELECT
       v.patient_visit_id
       , v.vent_day_date
       , v.vent_day
       , v.pos_VAE_doe_order
       , 0 AS prev_doe
       , 1 AS doe_flag
   FROM
       #vent_days AS v
   WHERE
       v.pos_VAE_doe_order = 1
       AND v.pos_VAE_doe_flg = 1
   UNION ALL
   /*  second query recurses across the anchor query*/
   SELECT
       v.patient_visit_id
       , v.vent_day_date
       , v.vent_day
       , v.pos_VAE_doe_order
       /*  when the previous VAE date of event occurs less than 14 days early
           then do not change prev_doe, otherwise set prev_doe equal to vent_day
           and assign a 1 to doe_flag. */
       , CASE   WHEN v.vent_day - VAE_doe.doe_vent_day >= 14
               THEN VAE_doe.vent_day ELSE VAE_doe.prev_doe END AS prev_doe
       , CASE   WHEN v.vent_day - VAE_doe.doe_vent_day >= 14
               THEN 1 ELSE 0 END AS doe_flag
   FROM
       #vent_days AS v
   INNER JOIN
       VAE_doe
       ON v.patient_visit_id = VAE_doe.patient_visit_id
       AND v.pos_VAE_doe_order = VAE_doe.pos_VAE_doe_order + 1 /*for each visit recurse by order of possible VAE days of envet*/
   WHERE
       v.pos_VAE_doe_flg = 1
   ) 


SELECT
   v.patient_visit_id
   , v.vent_day_date
   , v.vent_day
   , COALESCE(VAE_doe.doe_flg,0) AS doe_flg 
FROM
   #vent_days AS v 
LEFT OUTER JOIN
   VAE_doe
   ON v.patient_visit_id = VAE_doe.patient_visit_id
   AND v.vent_day = VAE_doe.vent_day 
ORDER BY
   v.patient_visit_id
   , v.vent_day 
OPTION(MAXRECURSION 25);

If I had read Brad Schulz’s post before coming up with this I would have used a WHILE loop. Recursive CTEs do not use set-based processing but process everything row-by-row using a Stack Spool. I don’t know if I will ever use a recursive CTE again. 

Besides a possible argument for convenience, does a legitimate reason for using a recursive CTE in SQL Server exist?