Update latest record with previous record values

Question

I have a table called Audits that has a CompanyId, Date, AuditNumber field, each Company can have many audits and the AuditNumber field keeps track of how many audits said company has.

I'm trying to update all latest audit records date with it's previous date + 5 years, so say CompanyId 12345 has 3 audits, I want to update the 3rd audit (3rd audit being the latest one) records date with the 2nd audit records date + 5 years into the future, etc... basically doing this to all the latest records.

What I've got so far is trying to use a while loop to do this but I'm pretty stuck as it's not exactly doing what I want it to...

DECLARE @counter INT = 1;
WHILE(@counter <= (SELECT COUNT(*) FROM Audits WHERE AuditNumber > 1)
BEGIN
    UPDATE Audits
    SET Date = CASE
                   WHEN AuditNumber > 1 THEN (SELECT TOP 1 DATEADD(YEAR, 5, Date) FROM Audits WHERE AuditNumber < (SELECT(MAX(AuditNumber) FROM Audits))
                   END
    WHERE AuditNumber > 1
    SET @counter = @counter + 1
END

I'm no expert on SQL, but this just updates the Date with the first previous date it can find due to the SELECT TOP(1) but if I don't put that TOP(1) the subquery returns more than 1 record so it complains.

Any help would be appreciated.

Thanks!

Answer 1

No need for a procedure and a loop. I would recommend window functions and an updatable cte for this:

with cte as (
    select date, 
        row_number() over(partition by company order by auditnumber desc) rn,
        lag(date) over(partition by company order by auditnumber) lag_date
    from audits
)
update cte 
set date = dateadd(year, 5, lag_date) 
where rn = 1 and lag_date is not null

The common table expression ranks records having the same company by descending audit number, and retrieves the date of the previous audit. The outer query filters on the top record per group, and updates the date to 5 years after the previous date.

You did not tell what to do when a company has just one audit. I added a condition to no update those rows, if any.