Thank you all for the help!  Phil's suggestion worked for me, I was trying to avoid it as there is no leading index on the date columns.

I as well...

I don't see why there should be a cast-iron association between tableAID and LastUpdateDateTime, assuming that any row, old or new, can be updated (and thus have its LastUpdateDateTime modified).

Instead,...

While this is an interesting technique, I'd suggest that the C# script could easily be modified to do the actual copy/move, obviating the need for a Foreach loop. This would,...

Something like this, perhaps.

PrevSickBalance = LAG(Accrue_Sick_Balance,1,0) OVER (Partition By Employee_ID ORDER BY Payroll_Detail_ID)

• This reply was modified 2 years ago by Phil Parkin.

How about providing your sample data in consumable format, along with your desired results? You've been here long enough to know better

That's a huge chunk of code and, in my opinion, asking such general questions is the sort of thing you'd employ a paid consultant to help with.

If you take the...

In the one that doesn't work, I suspect it's because the TOP (1) is being applied before the join on StaffId.

If you were to push the StaffId into the subqueries...

• This reply was modified 2 years ago by Phil Parkin.

Then you will need to...

You appear to be asking us to do your assignment for you, am I right? That table structure does not exist in the real world

My thoughts are that any password columns should be encrypted. No need to worry about DENY permissions if you do that.

This is not an easy format to achieve what you want, because the array items are not linked to each other.

But if you were to add a key to the...

I get that. Just substitute your...

Added local variables to make the code work in isolation. Your best bet is to output as a string:

DECLARE @J1 INT = 1;
DECLARE @JDate DATETIME = GETDATE...

• This reply was modified 2 years ago by Phil Parkin.

Do you have any other local drives with available space?

Does the schema of the Access DB also change much?

If not, it would be better, in my opinion, to build a process to refresh the data in your SQL Server...