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.
精彩评论