how to select a moving difference

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

Transact-SQL (2000)

how to select a moving difference

Author

Topic

ctuck
Starting Member

2 Posts

Posted - 2007-06-01 : 18:54:41

I have a table that has a date field and a numeric accumalation field. ex:

date_column total_accumulation_column
5/1/2007 312
4/1/2007 300
3/1/2007 280

I'm trying to create a query that will return the accumalated difference for each month. I'm trying to achieve a result set like this:

date difference
5/1/2007 12
4/1/2007 20

Can anyone show me how to achieve these results?

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-01 : 19:48:38

The example you gave is a simple difference, not an accumulated difference. Here is how to implement it:


select	A.date_column
	A.total_accumulation_column
	A.total_accumulation_column - B.total_accumulation_column as difference
from	[YourTable] A
	left outer join [YourTable] B on A.date_column = dateadd(month, -1, B.date_column)

e4 d5 xd5 Nf6

ctuck
Starting Member

2 Posts

Posted - 2007-06-04 : 15:27:57

Thank You, that was just what I was looking for. Why the outer join vs inner.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-04 : 17:03:15

Because the very earliest date value from instance A will not match with a prior datevalue from instance B, and presumably you would want it to show up. Without an outer join you would not get the full recordset.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources