0

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?

2
  • You should add your code that you've tried so far, especially for the ROW_NUMBER() window attempt, as that's the correct way to go about this. But 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.
    – J.D.
    Sep 7, 2023 at 12:11
  • Row_Number() will work.Also you should have one column called CreatedDate datetime in PostHolder table. IMHO,you should play and analyse with query inside CTE .Then you will realize that there are some unnecessary condition in where clause and query can become far simpler.
    – KumarHarsh
    Sep 8, 2023 at 5:10

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.