Storing and Querying Entity Lineage

Question

Assume there is a table of contract data, aptly named dbo.Contracts. Once a contract expires, a new row will be created in the table. I need to track the contract number of the old contract with the new contract. For example, contract number 123456 expires today. The application creates a renewal contract today (contract number 888888). Field dbo.Contracts.PreviousContractID for the row belonging to contract number 888888 gets updated to 123456.

That's all well. However, eventually over the years there will be a "chain" of contracts. 123456 renewed as contract 888888, which renewed as 999999, etc, etc.

----------------------------------------------------------------
Table dbo.Contracts
----------------------------------------------------------------
ContractID | Lots of other fields | PreviousContractID
----------------------------------------------------------------
123456     |                      | NULL
----------------------------------------------------------------
888888     |                      | 123456
----------------------------------------------------------------
999999     |                      | 888888

How would I write a query to say "given contract number 999999, query all contracts in the chain." I'm at a loss at where to start. I'm not even sure adding a field dbo.Contracts.PreviousContractID is even the right design.

Desired query would say, "For contract number 999999, get all the contracts in the 'chain'":

@ContractID = 999999
Result set:
----------
ContractID
----------
999999
----------
888888
----------
123456
----------

I'm not looking for a complete code solution, rather a bump in the right direction in design. This feels almost like a "recursive self-join", if there is such a thing, and I'm lost on it.

You should look into [Common Table Expressions](https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx). — devlin carnate, May 11 '17 at 18:54
...is even the right design. Neither am I. The "right" design depends on your goal and what you are trying to model. Perhaps you need to form some sort of parentage among "extensions" - which implies at least one other table and a change in perspective. Sometimes you need to try different approaches with your tables and validate them against your data/goals. — SMor, May 11 '17 at 19:07
@ZoharPeled I sincerely meant no disrespect. I didn't even know my vote to reopen would reverse your vote. That said, you linked to a Top Down while OP needed a Bottom Up — John Cappelletti, May 11 '17 at 19:18
No problem, I was just wondering. IMHO, almost all recursive cte questions are duplicates, so I was surprised, that's all. — Zohar Peled, May 11 '17 at 19:21
@JohnCappelletti - so there's no duplicate bottom up CTE questions on SO? How about this one: http://stackoverflow.com/questions/4690324/sql-recursive-query — devlin carnate, May 11 '17 at 20:51
@devlincarnate Never said this question was not a dupe. My contention is that it was linked to an inappropriate answer. That's all. — John Cappelletti, May 11 '17 at 20:59
@JohnCappelletti - sorry, just frustrated that a high rep member can't be bothered to actually mark an obvious dupe as a dupe. — devlin carnate, May 11 '17 at 21:01
@devlincarnate I'm sorry your frustrated, but If you'll endulge me, I have just a few of points. 1) Sometimes people need a little kickstart i.e. seeing their data in action. 2) HardCode is an active contributor (not a leech) 3) Virtually all of his questions were well recieved. 4) From what I've witnessed, he is always respecful and grateful (very important to me). And finally 5) I suspect we all have dupes in one fashion or another. As the old saying goes "Nothing new under the sun." — John Cappelletti, May 11 '17 at 21:29
This question is being discussed on Meta: https://meta.stackoverflow.com/q/349074/2751851 (pinging @JohnCappelletti). — duplode, May 11 '17 at 22:41

score 1 · Accepted Answer · answered May 11 '17 at 18:58

A simple recrusive CTE should do the trick

Declare @YourTable Table ([ContractID] varchar(50),[PreviousContractID] varchar(50))
Insert Into @YourTable Values
 (123456,NULL)
,(888888,123456)
,(999999,888888)

Declare @ContractID  int = 999999     

;with cteHB as (
      Select [ContractID]
            ,[PreviousContractID]
            ,Lvl=1
      From   @YourTable
      Where  [ContractID]=@ContractID 
      Union  All
      Select R.[ContractID]
            ,R.[PreviousContractID]
            ,P.Lvl+1
      From   @YourTable R
      Join   cteHB P on P.[PreviousContractID] = R.[ContractID])
Select Lvl  
      ,A.[ContractID]
      ,A.[PreviousContractID]
From cteHB A
Order By 1

Returns

Lvl ContractID  PreviousContractID
1   999999      888888
2   888888      123456
3   123456      NULL

@HardCode Happy it helped. – John Cappelletti May 11 '17 at 19:14 — John Cappelletti, May 11 '17 at 19:14

Storing and Querying Entity Lineage

1 Answers1