question

DataMedici avatar image
DataMedici asked

Create column that looks up and calculates based on a previous value

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]
sql-server-2012columnlookupscalculated-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Squirrel avatar image
Squirrel answered
; with CEO_Exp as ( select exp.[Date], exp.[Exp], ceo.CEO_Name from [Expense_Table] exp inner join [CEO_Table] ceo on ceo.FromDate exp.Date ) select *, PercentChange = (c.[Exp] - [LastCEOExp]) * 100 / l.[LastCEOExp] from [CEO_Exp] c outer apply ( select top 1 LastCEOExp = [Exp] from [CEO_Exp] x where x.[Date] < c.[Date] and x.[CEO_Name] c.[CEO_Name] order by x.[Date] desc ) l
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.