audit reconstruction

--set up dummy tables. These should match everywhere that's important
declare @prodTable table (
    UserID int,
    AboutMe nvarchar(100)
)

declare @testAudit table (
    UserID int,
    ChangeDate date,
    AboutMe_Before varchar(100)

)

--either comment this or change the user ID to test 'row deleted' functionality
insert into @prodTable (UserID, AboutMe) values
(26837, 'This is not null'),
(26838, 'abcde')

--note dates are different. I just need a little time difference in order to sort.
--This assumption should hold for real auditing anyway - changes will be at different times
insert into @testAudit (UserID, ChangeDate, AboutMe_Before) values
(26837, '2024-01-01', '<p>I make Microsoft SQL Server etc etc'),
(26838, '2024-01-02', 'original'),
(26837, '2024-01-03', 'Update Numero Uno'),
(26838, '2024-01-04', 'an update'),
(26837, '2024-01-05', null);

--append the current value in the prod table as an 'audit' for each user id.
--Also add an extra column - lastUpdate, so that the last row is not shown
with a as (
    select *, 0 as lastUpdate from @testAudit

    union

    select t.UserID,
    getdate() as ChangeDate,
    case when max(p.AboutMe) is null then 'row deleted' else max(p.AboutMe) end as AboutMe,
    1 as lastUpdate
    from @testAudit t
    left join @prodTable p on t.UserID = p.UserID
    group by t.UserID
),

--extra cte so the lead function doesn't cut off
b as (
    select
    UserID,
    ChangeDate,
    AboutMe_Before,
    lead(aboutMe_Before,1) over (partition by UserID order by changeDate) as AboutMe_After,
    lastUpdate
    from a
)

--remove the last update
select * from b where lastUpdate = 0