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:
|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:
- If the PersonID exists in PostUpdates but not in PostHolders, that's a new row and should be appended.
- If the PersonID and the AppointmentID match, other data (such as the Job title) which has changed should be considered an update.
- 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:
|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:
|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?