How do you write a query to find the original data from the history table?

Question

My table data:

PolicyId    PolicyEndorsementId OldFireLocationId NewFireLocationId
----------- ------------------- ----------------- -----------------
2167        2846                3298              4460
2167        2846                3299              4461
2167        2848                4460              4462
2167        2848                4461              4463
2167        2849                4462              4464
2167        2849                4463              4465
2167        2849                null              4466
2167        2850                4464              4467
2167        2850                4465              4468
2167        2850                4466              4469

This is a history table where the FireLocationId 3298 and 3299 are the original FireLocationId, and they have been replicated into new ids which are 4460 and 4461 respectively. Again 4460 and 4461 have been replicated to 4462 and 4463 respectively. This goes on and on with new PolicyEndorsementId for each replication.

Also, for the row with null OldFireLocationId, OriginalFireLocationId will be the NewFireLocationId.

I need to find out original FireLocationId as shown in the output below:

PolicyId    PolicyEndorsementId OldFireLocationId NewFireLocationId  OrignalFireLocationId
----------- ------------------- ----------------- ----------------- ---------------------
2167        2846                3298              4460                3298
2167        2846                3299              4461                3299
2167        2848                4460              4462                3298
2167        2848                4461              4463                3299
2167        2849                4462              4464                3298
2167        2849                4463              4465                3299
2167        2849                null              4466                4466
2167        2850                4464              4467                3298
2167        2850                4465              4468                3299
2167        2850                4466              4469                4466

Answer 1

Here is one way using Recursive CTE

;WITH cte
     AS (SELECT *,
                Isnull(OldFireLocationId, NewFireLocationId) AS parent,
                level = 1
         FROM   Yourtable
         UNION ALL
         SELECT a.*,
                c.parent,
                level =level + 1
         FROM   Yourtable a
                INNER JOIN cte c
                        ON a.PolicyId = c.PolicyId
                           AND a.OldFireLocationId = c.NewFireLocationId)
SELECT TOP 1 WITH ties PolicyId,
                       PolicyEndorsementId,
                       OldFireLocationId,
                       NewFireLocationId,
                       OrignalFireLocationId = parent
FROM   cte 
ORDER  BY Row_number()OVER(partition BY PolicyId, PolicyEndorsementId,Isnull(OldFireLocationId, NewFireLocationId)
              ORDER BY level DESC) 

Answer 2

You can try this:

DECLARE @DataSource TABLE
(
     [PolicyId] INT
    ,[PolicyEndorsementId] INT
    ,[OldFireLocationId] INT 
    ,[NewFireLocationId] INT
);

INSERT INTO @DataSource ([PolicyId], [PolicyEndorsementId], [OldFireLocationId],[NewFireLocationId])
VALUES   (2167, 2846, 3298, 4460)
        ,(2167, 2846, 3299, 4461)
        ,(2167, 2848, 4460, 4462)
        ,(2167, 2848, 4461, 4463)
        ,(2167, 2849, 4462, 4464)
        ,(2167, 2849, 4463, 4465)
        ,(2167, 2849, NULL, 4466)
        ,(2167, 2850, 4464, 4467)
        ,(2167, 2850, 4465, 4468)
        ,(2167, 2850, 4466, 4469);

WITH DataSource AS
(
    SELECT DS1.*
          ,DS1.[NewFireLocationId] AS Original
          ,0 AS [Level]
    FROM @DataSource DS1
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM @DataSource DS2
        WHERE DS2.[OldFireLocationId] = DS1.[NewFireLocationId]
    )
    UNION ALL
    SELECT DS2.*
          ,DS1.[Original]
          ,DS1.[Level] + 1
    FROM DataSource DS1
    INNER JOIN @DataSource DS2
        ON DS1.[OldFireLocationId] = DS2.[NewFireLocationId]
),
TempDataSource AS
(
    SELECT DS1.[Original]   
          ,ISNULL(DS2.[OldFireLocationId], DS2.[NewFireLocationId]) AS [NewValue]
    FROM
    (
        SELECT [Original]
              ,MAX([Level]) AS [Level]
        FROM DataSource
        GROUP BY [Original]
    ) DS1
    INNER JOIN DataSource DS2
        ON DS1.[Original] = DS2.[Original]
        AND DS1.[Level] = DS2.[Level]
)
SELECT A.[PolicyId], A.[PolicyEndorsementId], A.[OldFireLocationId], A.[NewFireLocationId], B.[NewValue]
FROM DataSource A 
INNER JOIN TempDataSource B
    ON A.[Original] = B.[Original]
ORDER BY A.[PolicyId]
        ,A.[PolicyEndorsementId]
        ,IIF(A.[OldFireLocationId] IS NULL, 1, 0)
        ,A.[OldFireLocationId];

It will give you:

As we are not able to define by your table structure which value is first and last directly, we need to calculated this using two CTE.

The first CTE is recursive. The first part of it get all last values - this is value, which does not have NewFireLocationId value. The recursive part of the CTE gets the link from the last to the first one. We have two additional columns - Level and Original. The result looks like this:

We are going to use this recalculated columns in the second CTE to get the OrignalFireLocationId. For each Original value we are getting the maximum Level - from there we get this value. The result is like this:

And the final part is just joining the two CTE results and selecting only the column we need.