Retrieving Current and Historical Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving Current and Historical Data

Author

Topic

btisdabomb2
Starting Member

12 Posts

Posted - 2007-06-28 : 10:32:57

I have a table that contains financial data for various organizations. This table has data for every quarter of the past 5 years for these organizations.

What I need to do is select the current data, along with data from the past 3 year ends for a specific organization. This I can do.

While doing this select I need to also include calculated fields such as Return on Assets for the current year and each of the past 3 year ends. This I have no idea how to do.

How would I go about doing this considering this field is calculated as "NetIncome / Avg(CurrentAssets + PreviousYearAssets)"

I have all of this data in one table, I just don't know how to reference the row for the previous year to do some of these calculations.

Hopefully that makes sense, and any help would be appreciated!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-28 : 11:46:27

look into "self joins"...you basically can go

select * from table1 a
left join table1 b on a.cust = b.cust and a.date1 = b.date + 1 (year)
(use the dateadd function for this latter bit)

posting sample data, ddl and matching expected results will also advance things...see the FAQ list in the "New to SQL Server" forum...

btisdabomb2
Starting Member

12 Posts

Posted - 2007-06-28 : 13:37:52

Well I can't post any real data/tables/etc per company policy. So lets say I have a table that looks like the following...

AccountID | Date | NetIncome | TotalAssets
1         | 2006 | 1000      | 5000
2         | 2006 | 5400      | 11450
3         | 2006 | 6810      | 89410
1         | 2005 | 2000      | 7000
1         | 2004 | 3000      | 9000
1         | 2003 | 4000      | 11000

I need to calculate the return on assets for accountID 1 for the years 2006, 2005, and 2004

ROA = NetIncome / Avg(TotalAssets "CurrentYear" + TotalAssets "PreviousYear")

My result set needs to look like this...

AccountID | Date | ROA
1         | 2006 | 0.1666
1         | 2005 | 0.25
1         | 2004 | 0.3

How would I go about doing that?

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-28 : 13:46:32

The Rated R superstar would post a dll ;)

btisdabomb2
Starting Member

12 Posts

Posted - 2007-06-28 : 16:54:55

Well I found a way to make it work using AndrewMurphy's suggestion. It didn't make sense to me at first, but it finally "clicked" in my head once I tried it. Thanks!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-29 : 06:28:16

Glad to hear I don't issue nonsense all the time.!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources