Update the second last row for each group

Question

I am building a database in SQL Azure, with an MS Access front end.

I have a set of data in a table called PostHolders which looks like this:

ID	PersonID	ApppointmentID	ContractStartDate	ContractEndDate	PostTitle
1	1	ABC-123	1 Jan 23	30 June 23	Tutor
2	2	DEF-234	1 Feb 23		Manager
3	3	EFG-345	2 Mar 23		Supervisor

Into this table, I will be importing new rows, from a table called PostUpdates which essentially matches this table in structure, without the ID col. This PostUpdates table contains a row for each person, whether or not there's been a change. It may also contain new people.

The rules are:

1. If the PersonID exists in PostUpdates but not in PostHolders, that's a new row and should be appended.
2. If the PersonID and the AppointmentID match, other data (such as the Job title) which has changed should be considered an update.
3. If the person ID is the same, but the Appointment ID is different, this indicates that the person has taken a new role, and the row should be appended to the existing data.

All this I'm fine with.

However, the logic also dictates that in the latter case, the person's previous ContractEndDate should be updated with the day before the new row's start date. So, if the new data contains this:

PersonID	ApppointmentID	ContractStartDate	ContractEndDate	Job title
2	DEF-235	1 Jun 23		Professor

This new row should be appended, but we should also update row 2 in the table to read:

ID	PersonID	ApppointmentID	StartDate	EndDate	Job title
2	2	DEF-234	1 Feb 23	30 May 23	Manager

I could write a loop in VBA to do the job, but this seems highly inefficient. I've tried using an update statement, with a CTE and ROW_NUMBER() as a means of determining the sequence, then trying to find a row where the row number is one less than the max row number for each person, but again I seem to be tying myself in knots, and I feel there must be a more efficient way.

I can do this task before I import the new data, as part of that import, or as another task after the import - whatever makes most sense.

Any help appreciated.

[EDIT] In response to J.D.'s comment, here's what I'm currently playing with. I've tweaked a couple of the column names above, as I'd simplified them in my original posting:

WITH CTE AS (
SELECT PersonID, AppointmentID, ContractStartDate, ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PostHolderID ASC) AS PostID
FROM PostHolders 
)
, CTEWithMax AS
(SELECT *, MAX(PostID) OVER (PARTITION BY PersonID) AS MaxPostID
FROM CTE
)

UPDATE hr.PostHolders
SET ActualEndDate=DATEADD(DAY,-1,ctewithmax.Contractstartdate)
FROM hr.PostHolders ph INNER JOIN CTEWithMax 
ON ph.PersonID = CTEWithMax.PersonID AND ph.AppointmentID = CTEWithMax.AppointmentID
WHERE CTEWithMax.PostID=MaxPostID-1
AND ph.ActualEndDate IS NULL

I decided that the way to do this was to import the data first, with the resultant multiple rows for people who've got a new role.

This seems to work... as far as I can tell based on mocked-up data, but I'm uneasy as it's hard to test without real data, which I don't currently have.

J.D. you said that

you shouldn't have to worry about figuring out "the row number [that] is one less than the max row number". It sounds like the ORDER BY clause just needs to be ordered in the opposite direction and then you can always select the 2nd row consistently

I'm still using the "one less than the max row number" that I'd tried earlier - am I missing something here?

Answer 1

I'm still using the "one less than the max row number" that I'd tried earlier - am I missing something here?

Yes, as I mentioned in the comments, if you switch your ORDER BY clause's direction from ascending to descending then the first row will always be your max (the new row you just inserted into your table) and your second row will always be the second to last row, the one you want to update. You would no longer need to calculate the max row and then do math to get the second to last row. So it saves you a CTE and simplifies the code.

Notice the change I made to the ORDER BY clause below (you'll also want to select the PostHolderID here for joining to later):

WITH CTE AS (
SELECT PostHolderID, PersonID, AppointmentID, ContractStartDate, ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PostHolderID DESC) AS PostID
FROM PostHolders 
)

Then you no longer need CTEWithMax and your update statement can be simplified to just:

UPDATE hr.PostHolders
SET ActualEndDate=DATEADD(DAY,-1,MaxRow.Contractstartdate)
FROM hr.PostHolders
INNER JOIN CTE AS SecondToLast
    ON PostHolders.PostHolderID = SecondToLast.PostHolderID
    AND SecondToLast.PostId = 2 -- The second to last row per partition grouping
INNER JOIN CTE AS MaxRow
    ON SecondToLast.PersonID = MaxRow.PersonID
    AND MaxRow.PostId = 1 -- The max row per partition grouping

---

There's actually a way to simplify the update statement further, by updating the root table through the CTE itself (without needing to join back to the original table). But there are some limitations on when one can do that, so I'm not going to go that far right now.