How to use Recursive CTE on Effective Dates

Question

The Setup file below contains all the data that is needed to answer the question.

Sample file

End result

Problem: As you can see in the Sample File Screenshot I have inherited a database that stores the EffectiveDates for when an Entity began their association with a specific EntityLocation. In the example image Entity 10170's most recent association with an EntityLocation was 2011-01-01, before that it was 2006-01-01, and so on... (See Sample File Screenshot)

Question: Using a Recursive CTE on the sample data I would like the return result to look like the End Result Sample, how do i accomplish this using Recursive CTE and NOT Cursors. (See End Result Sample)

Setup File: sample xlsx which contains the full data.

Answer 1

This reaction is not about CTE, but about getting you to a solution for this particular problem. You do not need recursion here. Recursion is only neccessary when you have to iterate a link to itself multiple times. If you want to study CTE try to make e.g. an organogram.

Your solution is about linking the EffectiveDate as an Opendate to another EffectiveDate as a Closedate if there is one.

First you may want to create an Location index per entity using the ROW_NUMBER() function with partitioning. So my new table location will be defined as

SELECT ROW_NUMBER() OVER (partition BY EntityId ORDER BY EntityEffectiveDate ASC) as LocId, 
    EntityId, EntityLocation, EntityEffectiveDate 
INTO Location 
FROM YourOldEntityLocation

Now you can link the effectivedate for each location (equal entityId's) to a next effectivedate of the location (LocId+1) if there is one (LEFT JOIN).

SELECT opendate.LocId, opendate.EntityId, opendate.EntityLocation, opendate.EntityEffectiveDate,
   isnull(closedate.EntityEffectiveDate,'2050-01-01') as EffectiveToDate
FROM Location as opendate 
   LEFT JOIN Location as closedate 
      ON closedate.entityId = opendate.entityId 
      AND opendate.LocId+1 = closedate.LocId
ORDER BY opendate.EntityId, EntityEffectiveDate DESC

Which results in:

LocId   EntityId    EntityLocation  EntityEffectiveDate EffectiveToDate
3   10170   63  2011-01-01  2050-01-01
2   10170   31  2006-01-01  2011-01-01
1   10170   43  2003-07-01  2006-01-01
3   11674   45  2014-07-01  2050-01-01
2   11674   29  2004-10-01  2014-07-01
1   11674   45  2003-04-01  2004-10-01
3   11675   45  2011-04-01  2050-01-01
2   11675   29  2004-10-01  2011-04-01
1   11675   45  2003-04-01  2004-10-01

as per your request.

It could be a suggestion not to replace the NULL value at all. In accordance to @Jack's reaction you then do not have to dream up a future date. The result simple tells you there is no closedate to this EntityLocation.

It's simple to select the actual locations by selecting the NULL value in this column. The result would be:

LocId   EntityId    EntityLocation  EntityEffectiveDate EffectiveToDate
3   10170   63  2011-01-01  NULL
2   10170   31  2006-01-01  2011-01-01
1   10170   43  2003-07-01  2006-01-01
3   11674   45  2014-07-01  NULL
2   11674   29  2004-10-01  2014-07-01
1   11674   45  2003-04-01  2004-10-01
3   11675   45  2011-04-01  NULL
2   11675   29  2004-10-01  2011-04-01
1   11675   45  2003-04-01  2004-10-01