I have a two table table, first table storing all the latest data and second table to capture the changed data. For example if an user change value the FIRST table will update the data and SECOND table will store the previous data and new data with a unique revision id.

Problem: If I want to see the revision 4 data I'm not sure how to backtrack the values and find the exact data in table 1 during that time

DECLARE @Projects TABLE(
    ProjectId int,
    ProjectName VARCHAR(255),
    StartQuarter int,
    EndQuarter int,
    StartYear int,
    EndYear int,
    Description varchar(255)
);

DECLARE @History TABLE(
    ProjectId int,
    RevisionId int,
    Attribute varchar(255),
    Previous varchar(255),
    New varchar(255)
);

INSERT INTO @Projects (ProjectId,ProjectName,StartQuarter,EndQuarter,StartYear,EndYear,Description)
VALUES (1,'P1', 1,4,2022,2022,'Test')

INSERT INTO @History (ProjectId ,   RevisionId ,    Attribute , Previous ,New )
values (1,2,'ProjectName', 'Project 1', 'Projects')
,(1,3,'Description', 'new', 'newtest')
,(1,4,'ProjectName', 'Projects', 'Alpha')
,(1,5,'Description', 'newtest', 'Test')
,(1,6,'ProjectName', 'Alpha', 'P1')

Currently after all the changes done the table looks like this:

ProjectId   ProjectName StartQuarter    EndQuarter  StartYear   EndYear   Description
1           P1          1               4           2022        2022      Test

Now I want to see data during RevisionId = 4, the output should be showing something like this:

ProjectId   ProjectName StartQuarter    EndQuarter  StartYear   EndYear   Description
1           Alpha       1               4           2022        2022      newtest

CodePudding user response：

One way to do this is using outer apply's

select p.ProjectID,
       isnull(n.New, p.ProjectName) as ProjectName,
       p.StartQuarter,
       p.EndQuarter,
       p.StartYear,
       p.EndYear,
       isnull(d.New, p.Description) as Description
from   @Projects p

  outer apply ( select top 1
                       h.New
                from   @History h 
                where  h.ProjectId = p.ProjectId
                and    h.Attribute = 'ProjectName'
                and    h.RevisionId <= 4
                order by h.RevisionId desc
              ) n

  outer apply ( select top 1
                       h.New
                from   @History h 
                where  h.ProjectId = p.ProjectId
                and    h.Attribute = 'Description'
                and    h.RevisionId <= 4
                order by h.RevisionId desc
              ) d

the result is