CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-updatewindow-functions
SC.Cee
SC.Cee

Reputation: 237

Update latest record with previous record values

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!

Upvotes: 1

Views: 236

Answers (2)

Shahbaz
Shahbaz

Reputation: 178

You must add row_number to you result_tbl first then join result_tbl with self ON Al.CompanyId=A2.CompanyId AND Al.IND=1 AND A2.IND=2, now you have latest record and previous record in one record, and you can update original table

WITH A AS
(
    SELECT *,ROW_NUMBER(PARTITION BY CompanyId ORDER BY AuditNumber DESC) IND FROM Audits
),B AS 
(
    SELECT Al.CompanyId,A1.AuditNumber,A2.[DATE] FROM A A1 INNER JOIN A A2 ON Al.CompanyId=A2.CompanyId AND Al.IND=1 AND A2.IND=2
)UPDATE _Audits SET _Audits.[Date]= DATEADD(YEAR,5,B.[DATE]) FROM
B LEFT JOIN Audits _Audits ON B.CompanyId=_Audits.CompanyId AND B.AuditNumber=_Audits.AuditNumber

Upvotes: 0

GMB
GMB

Reputation: 222582

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.

Upvotes: 1

Related Questions