Sean, I told you I wasn't giving up. Here is my latest attempt.

with Vehicles as

(

select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName,

...

Hmm. I thought you couldn't update a table using an aggregate query (ie. distinct keyword or group by). You have already gone way above and beyond the call...

Sean, to answer your question, the row numbers must change (reinitialize) based on return dates so it will partition (group) correctly. If you'll look at the table I sent,...

Sean, I haven't heard anything in a while so I assume you are on vacation. I don't know if you have had time to look at this.

Sean, you must be a brilliant guy to come up with such elegant code. So I figured it must be my explanation. I racked my brain and came...

Sean, I'm glad you brought the row number up. It will help me illustrate my point.

RegionProgYearRiskNonRiskFlagMFGLeaseCycleModelName

Southeast 12 R NIS 1 ALTIMA S

The way it is...

Sean,

I'm realizing something. Let's say that you have three records in the ReturnsPros table that have the same data in six columns:

ProgYear = '13'

RiskNonRiskFlag = 'N'

Region = 'Central'

ModelName =...

This is a tough one but I think I have finally identified the problem. If you look at the ReturnsPros records (even the ones you had for this project)...

I MAY have success finally!

If I run the same query below against VehiclesToUpdate I get 9. So the update can choose "X" number of VINs not caring if the...

Correction:

WHERE NewReturnDate is null

Sean, I know you're busy so any help you give me is appreciated. It doesn't go with the territory that you have to support something you gave me as...

Sean, here is the "unsolved mystery". When I run your program pretty much as written, I get about 70,000 rows that don't update (null Return dates). Included in...

Sean, I devised a VBA procedure that creates SQL Code. I tried running the code below and look at the results following.

Select count(*), 'Update' as SQLName from VehiclesToUpdate Where...

Sean, my question is - if the VehiclesToUpdate is obtained from the partitions in the VehicleDetail, why wouldn't they match the VehicleDetail? As you can see from the results,...

Sean, you may not be there today but I made an interesting discovery. I tried four groupings for totals. For example, I took the group represented by:

Select *

From...