OK there are a few thing that we still missing but I pieced together. First thing is in your original logic you updated the returns in order of delivery date but this was missing in your ddl. No biggie.

Also, since dealing with table names with spaces is such a pain I removed them on my side.

alter table VehicleDetail

add DeliveryDate datetime

--now we need to have some sort of delivery date. I just used some existing data to generate some randomish dates.

update VehicleDetail

set DeliveryDate = dateadd(dd, datediff(dd, 0, DATEADD(day, -1 * cast(right(VIN,2) as int), GETDATE())), 0)

from VehicleDetail

OK so now we have a table that should be pretty close to your actual table. I did make a few adjustments to the returns to make sure this works when the return amount is less than the amount delivered.

update ReturnsPros set UnitCount = 1 where Region = 'Atlantic'

update ReturnsPros set UnitCount = 2 where Region = 'Central'

update ReturnsPros set UnitCount = 9 where Region = 'Southeast'

Alright so now we have a setup that is somewhat realistic...but we still need to solve the actual issue. Instead of a cursor or a while loop I am using the ROW_NUMBER() windowed function to add row numbers. This let us know which rows to update based on the values in the return table. The remaining comments are in the code.

--first we need to get the RowNumber

with Vehicles as

(

select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate) as RowNum

from VehicleDetail

)

, VehiclesToUpdate as

(

--Now we only want to see the rows we want to update

select v.VIN,

v.SerialNumber,

r.ReturnDate,

v.ProgYear,

v.LeaseCycle,

v.ModelName,

v.RiskNonRiskFlag,

v.Region,

v.MFG,

v.ReturnDate as NewReturnDate --needs to have a unique name so we can update it.

from ReturnsPros r

join Vehicles v on r.ProgYear = v.ProgYear

and r.LeaseCycle = v.LeaseCycle

and r.ModelName = v.ModelName

and r.RiskNonRiskFlag = v.RiskNonRiskFlag

and r.Region = v.Region

and r.MFG = v.MFG

where v.RowNum <= r.UnitCount

)

update VehiclesToUpdate

set NewReturnDate = ReturnDate --since NewReturnDate is actually ReturnDate from VehicleDetail we know which column we are updating

--did it actually work?

select * from VehicleDetail where ReturnDate is not null

That should produce the results as I understand them from your posts.