Post reply

Recursive CTE help

  • September 20, 2017 at 9:02 am

    #149374

    Hi guys -- I could use some assistance with recursive CTEs.  I know the principle of what I want to do, but I don't know enough about recursive CTEs to know how to code this.

    Here's my scenario: let's say I have data that looks something like this:


    ID     ParentID     Date
    5      4            NULL
    4      3             NULL
    3      2            NULL
    2      1            2017-01-01
    1      NULL            NULL
    (note: data may not necessarily be in this order)

    Here's what I'm looking to do:

    1. If an ID has a date, return the ID and the date
    2. If the date for an ID is null, then repeat the process using the ParentID
    3. Repeat these steps until either it finds an ID with a date, or it gets to the end of the chain (and if it gets to the end without finding a date, return NULL for the date)

    I figure a recursive CTE would be ideal for this, but I'm having trouble trying to write the code.  I've written plenty of CTEs before, but none of them have been recursive.

    Or, for that matter, would something other than a recursive CTE work for this?

    Thanks, all!

    +--------------------------------------------------------------------------------------+
    Check out my blog at https://pianorayk.wordpress.com/

  • September 20, 2017 at 9:23 am

    #1960186

    Ray K - Wednesday, September 20, 2017 9:02 AM

    Hi guys -- I could use some assistance with recursive CTEs.  I know the principle of what I want to do, but I don't know enough about recursive CTEs to know how to code this.

    Here's my scenario: let's say I have data that looks something like this:


    ID     ParentID     Date
    5      4            NULL
    4      3             NULL
    3      2            NULL
    2      1            2017-01-01
    1      NULL            NULL
    (note: data may not necessarily be in this order)

    Here's what I'm looking to do:

    1. If an ID has a date, return the ID and the date
    2. If the date for an ID is null, then repeat the process using the ParentID
    3. Repeat these steps until either it finds an ID with a date, or it gets to the end of the chain (and if it gets to the end without finding a date, return NULL for the date)

    I figure a recursive CTE would be ideal for this, but I'm having trouble trying to write the code.  I've written plenty of CTEs before, but none of them have been recursive.

    Or, for that matter, would something other than a recursive CTE work for this?

    Thanks, all!

    Would you need something like this? If not, can you post your input and output?


    CREATE TABLE #Sample(
      ID  int,
      ParentID  int,
      Date  date);
    INSERT INTO #Sample
    VALUES
    (5, 4 , NULL),
    (4, 3 , NULL),
    (3, 2 , NULL),
    (2, 1 , '2017-01-01'),
    (1, NULL, NULL);

    DECLARE @ID int = 1;

    WITH rCTE AS(
      SELECT ID, ParentID, Date
      FROM #Sample
      WHERE ID = @ID
      UNION ALL
      SELECT r.ID, s.ParentID, s.Date
      FROM #Sample s
      JOIN rCTE r ON s.ID = r.ParentID
      WHERE r.Date IS NULL
    )
    SELECT *
    FROM rCTE
    WHERE Date IS NOT NULL

    GO
    DROP TABLE #Sample;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 20, 2017 at 9:34 am

    #1960189

    Well, the example data I posted pretty much is the input, starting with an ID that the code finds (I don't want to post actual data for security reasons).  As for output, this is actually part of a larger function.  Once I figure out this piece, I was going to tinker with it to see if I can get it to work.  For now, I'll settle for either a found date or null in the chain.

    I'll look at your code and see if I can get it to work in my scenario.

    Thanks!

    +--------------------------------------------------------------------------------------+
    Check out my blog at https://pianorayk.wordpress.com/

  • September 20, 2017 at 9:50 am

    #1960195

    Ray K - Wednesday, September 20, 2017 9:34 AM

    Well, the example data I posted pretty much is the input, starting with an ID that the code finds (I don't want to post actual data for security reasons).  As for output, this is actually part of a larger function.  Once I figure out this piece, I was going to tinker with it to see if I can get it to work.  For now, I'll settle for either a found date or null in the chain.

    I'll look at your code and see if I can get it to work in my scenario.

    Thanks!

    Since your post is the proverbial tip of the iceberg for something larger, please see the following "full Monty" articles on the subject.
    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

    Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply