Question on row references - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Question on row references

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-04-29 : 16:49:55

Okay, I have a question on row references which I know is not very set-based thinking, but please forgive me (I'm trying, ever so slowly, to adapt to the SQL way).

Let's say I have a table as follows:

Date ID Value

which is neatly normalized, has unique records per Date + ID, etc. What I often find I need to do is occasionally take a group of IDs from this table for which there are multiple observations recorded historically (hence the Date) and compare the values on these dates to each other. For instance, let's say we're looking at

Date ID Value
Jan1 ABC 510
Feb1 ABC 620
Mar1 ABC 680

Let's say I want to calculate the percent change between each month for ABC. How would I go about referring to the successive Value record for ID ABC in order to perform this calculation? Put another way, how would I retrieve:

select "Feb1%Chg" = [something which gives 620/510]

Also, let's say the months I have above are irregular (maybe I won't always know that the next record is exactly 30 days before or after the current one) - hence my interest in being able to reference specific rows (or maybe I should be using relational operators between the Dates, etc.)

I don't know if this would require some advanced subquery or use of a cursor (heaven forbid), but if there is an elegant way to do this, would some one mind telling me how to start thinking about operations like this? Any help is very much appreciated in my adaptation to this new way of thinking.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-29 : 20:39:03

Here's one way to do it. It uses a temp table to create a sequential rowid (identity column) and uses that to self join to previous record:


set nocount on
declare @tb table (dt smalldatetime, val int)
declare @temp table (rowid int identity, dt smalldatetime, val int)

insert @tb
select '1/1/2005', 510 union all
select '2/1/2005', 620 union all
select '3/1/2005', 680 union all
select '3/12/2005', 640

--generate sequetial rowids by inserting to table with identity and order by
insert @temp (dt,val)
select dt,val
from	@tb
order by dt asc

--self join to previous record
select	a.dt dt1
	,b.dt dt2
	,PctChange = ((b.val - a.val) / convert(numeric(9,2), a.val)) * 100
from	@temp a
JOIN	@temp b 
		ON a.rowid = b.rowid-1

Be One with the Optimizer
TG

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-02 : 09:38:39

TG,

As usual, I am indebted to you. Thank you very much for all the help you've given me.

-KidSQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources