How do I add a scalar over a partitioned column from a look up table?

Note: I'm using SQL Server 2008

Let’s say I have a table of cars, and a look up table of States.

          Main Table            
VIN  MILEAGE State ME_Date
AAA111  13000   CA  8/31/2010
AAA111  13000   CA  9/30/2010
AAA111  13000   CA  10/31/2010
BBB222  71000   NY  8/31/2010
BBB222  71000   NY  9/30/2010
BBB222  71000   NY  10/31/2010
CCC333  5500    AZ  8/31/2010
CCC333  5500    AZ  9/30/2010
CCC333  5500    AZ  10/31/2010

       Look up Table    
State   Avg_Monthly_Mileage
CA      1000
NY      1500
AZ      800

What I want to do is for each VIN #, look up the State and add the respective mileage for each month. Then, start the process over for the next VIN #.

So, the result would be:

          Main Table            
VIN MILEAGE State   ME_Date
AAA111  13000   CA  8/31/2010
AAA111  14000   CA  9/30/2010
AAA111  15000   CA  10/31/2010
BBB222  71000   NY  8/31/2010
BBB222  72500   NY  9/30/2010
BBB222  74000  开发者_JAVA百科 NY  10/31/2010
CCC333  5500    AZ  8/31/2010
CCC333  6300    AZ  9/30/2010
CCC333  7100    AZ  10/31/2010

Well, if i understand your question correctly, you can do something like this:

SELECT A.VIN, A.MILEAGE + (Id-1)*ISNULL(B.Avg_Monthly_Mileage,0) AS MILEAGE, A.[State], A.ME_Date
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY VIN, State) Id
        FROM dbo.MainTable) A
LEFT JOIN LookUpTable B
ON A.[State] = B.[State]

Of course, if you want to use the query above, you need to be aware of some considerations. First, it assumes that in the MainTable you can't have any duplicates on the fields VIN, MILEAGE, State and ME_Date. Also, i don't know what the result should be if a same VIN, MILEAGE and State have ME_Dates on the same month. Still, the query above gives you the result you asked for.