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 up with something that I think will get the point across. The following table should tell all. It is a sample VehicleDetail (actually "vehicles") table with a "ShouldBe" column added. Relate the ShouldBe to the ReturnDate and a light bulb should go off. This is what the row number should be.

That's what I want, but it is the ultimate "chicken before the egg" dilemma. The return dates are already there. They wouldn't be in an actual run. If they were there, it would be easy to partition on ReturnDate.

I only supplied the VD columns that are necessary. I couldn't use "Row_Number" (with underscore) because it was a keyword. The code is tested this time so it should be an easy test.

create table SampleVehDet

(

ProgYearVarChar(2),

LeaseCycleVarChar(2),

ModelNameVarChar(30),

RiskVarChar(2),

RegionVarChar(20),

MFGVarChar(5),

ReturnDateDateTime,

RowNumberInt,

ShouldBeInt

)

Insert Into SampleVehDet (ProgYear, LeaseCycle, ModelName, Risk, Region, MFG, ReturnDate, RowNumber, ShouldBe)

values('13','1','RIO 5DR LX','R','CENTRAL','KIA','4/21/2014',1, 1),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','4/21/2014',2, 2),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/9/2014',3, 1),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',4, 1),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',5, 2),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',6, 3),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/23/2014',7, 1),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/23/2014',8, 2),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',9, 1),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',10, 2),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',11, 3),

('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',12, 4)