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 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.