question

Hello all, I was hoping someone could point me in the right direction for writing a query that returns a calculated value that uses a lookup. For example, I have two tables, as shown below. I have joined them together in a view, but I need one additional column: CEO_Table: CEO_Name | FromDate | ToDate ----------------------------------------- Glen Bryant | 2000-11-30 | 2002-06-30 Bob Costa | 2002-6-30 | 2004-9-15 Gill Bogart | 2004-9-15 | 2009-10-01 Ben Olson | 2009-10-01 | 2010-08-10 Expense_Table: Date | AsOf_Expenses_Total_Millions (as Exp) ----------------------------------------- 2001-01-01 | 100 2002-01-01 | 300 2003-01-01 | 155 2004-01-01 | 350 2005-01-01 | 400 2006-01-01 | 600 2007-01-01 | 150 2008-01-01 | 200 2009-01-01 | 300 2010-01-01 | 500 I am attempting to use these two tables to construct a view that adds on 3 additional columns: CEO (looks up the CEO for the given date); LastCEOExp (looks up the previous CEO's last expense value); PercentChange (uses the LastCEOExp to calculate the percent change, using the formula (Exp - LastCEOExp)/(LastCEOExp) * 100) CEO_Expenses_Change_Over_Time: Date | Exp | CEO_Name | LastCEOExp | PercentChange ------------------------------------------------------------------- 2001-01-01 | 100 | Glenn Bryant | NULL | NULL 2002-01-01 | 300 | Glenn Bryant | NULL | NULL 2003-01-01 | 155 | Bob Costa | 300 | -48% 2004-01-01 | 350 | Bob Costa | 300 | 16% 2005-01-01 | 400 | Gill Bogart | 350 | 14% 2006-01-01 | 600 | Gill Bogart | 350 | 71% 2007-01-01 | 150 | Gill Bogart | 350 | -57% 2008-01-01 | 200 | Gill Bogart | 350 | -42% 2009-01-01 | 300 | Gill Bogart | 350 | -14% 2010-01-01 | 500 | Ben Olson | 300 | 66% I have gotten so far as to adding the CEO_Name column, but am having trouble with the LastCEOExp column. Once I have that column nailed down, I can put together the PercentChange column on my own. Anyone have any advice? I am guessing that I could use a CTE or a UDF for this task, but am unsure of where to start. Below is what I have: SELECT exp.[Date] ,exp.[Exp] ,ceo.[CEO_Name] FROM [Expense_Table] exp INNER JOIN [CEO_Table] ceo ON ceo.[FromDate] <= exp.[Date] exp.[Date] < ceo.[ToDate]