CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Richard Kelly
Richard Kelly

Reputation: 195

How to convert this T-SQL cursor operation into set-based

I have a medical reporting project that requires calculating "onset" dates from a list of diagnosis dates.

The first time a patient presents with a certain diagnosis, that's onset date #1. For the next 30 days, any subsequent diagnosis date counts as part of that onset #1, so those subsequent dates can be dropped.

After 30 days have passed, the next diagnosis date counts as onset #2.

And so on..

Example input

2015-09-10 (this is onset #1)
2015-09-19 (within 30 days from onset #1, so drop)
2015-09-29 (within 30 days from onset #1, so drop)
2015-10-17 (>= 30 days from onset #1, so this is onset #2)
2015-10-19 (within 30 days from onset #2, so drop)
2015-11-13 (within 30 days from onset #2, so drop)
2015-11-29 (>= 30 days from onset #2, so this is onset #3)

Example output

2015-09-10 (onset #1)
2015-10-17 (onset #2)
2015-11-29 (onset #3)

This can be done with a cursor, and a minimal example is included below.

I've heard it said that any cursor operation can be expressed as a set-based one. But I can't figure out how one would approach this particular algorithm in a set-based way because calculations rely on previous ones. I can't see how it could be accomplished in one set-based "pass".

Can it be done? If so, how?

Any solution should work in SQL Server 2012.

DECLARE @dx_list TABLE(dx_dt date);
INSERT INTO @dx_list(dx_dt)
    VALUES ('2015-09-10') --this is onset #1
    , ('2015-09-19') 
    , ('2015-09-29')
    , ('2015-10-17') --date is >= 30 days from last onset, so this is onset #2
    , ('2015-10-19')
    , ('2015-11-13')
    , ('2015-11-29'); --date is >= 30 days from last onset, so this is onset #3

DECLARE @mycursor AS cursor;
SET @mycursor = CURSOR FOR
SELECT dx_dt
FROM @dx_list
ORDER BY dx_dt; --make sure dates are in order

DECLARE @possible_dt AS date;
DECLARE @onset_list TABLE(onset_dt date);

OPEN @mycursor;
FETCH NEXT FROM @mycursor INTO @possible_dt;
--First date is always an onset date
INSERT INTO @onset_list(onset_dt) VALUES (@possible_dt);

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @mycursor INTO @possible_dt;
    --If this date is 30 days or more from last onset date, add it
    IF @possible_dt >= DATEADD(dd, 30, (SELECT MAX(onset_dt) FROM @onset_list))
    BEGIN
        INSERT INTO @onset_list(onset_dt) VALUES (@possible_dt);
    END
END

CLOSE @mycursor;
DEALLOCATE @mycursor;

--Show results
SELECT * FROM @onset_list;

Upvotes: 3

Views: 357

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it using a Recursive CTE :

;WITH OnsetDates AS (
   SELECT TOP 1 dx_dt
   FROM dx_list
   ORDER BY dx_dt

   UNION ALL

   SELECT dx_dt
   FROM (  
     SELECT d1.dx_dt,
            ROW_NUMBER() OVER (ORDER BY d1.dx_dt) AS rn
     FROM dx_list AS d1
     INNER JOIN OnsetDates AS d2 ON d1.dx_dt > d2.dx_dt
     WHERE DATEDIFF(d, d2.dx_dt, d1.dx_dt) >= 30 ) AS t
   WHERE t.rn = 1
)
SELECT *
FROM OnsetDates

The so-called anchor member of the CTE is just the top-level date. The recursive member fetches the next onset date: this is the first date that is past 30 days+ the date returned by the previous invocation of the recursive CTE.

Note that in order to get this first date we have to use ROW_NUMBER and a sub-query, since TOP 1 and ORDER BY are not allowed in the recursive member of the CTE.

Demo here

Upvotes: 5

Related Questions