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.
Author |
Topic |
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-03-12 : 14:52:16
|
Any help greatly appreciated, I'm struggling with this one.Assume my table has three fields: code, month, value. The month field is in the format yyyymm.Here's what I'm trying to achieve; assume the following.code month value1234 201201 751234 201202 951234 201203 80What I'm looking for as output is:code month value1234 201202 201234 201203 -15The difference between 201202 and 201201 is 20 (95-75)The difference between 201203 and 201202 is -15 (80-95)This seems impossible to achieve, does anyone have any pointers?Many thanks for taking the time to read. |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-03-12 : 18:14:08
|
[CODE]declare @MyTable table ( -- Stuff you should be supplying code int not null, month char(6) not null, value int not null )insert into @MyTable (code, month, value)values (1234, '201201', 75), (1234, '201202', 95), (1234, '201203', 80);with Ordered -- Possible solutionas (select code, month, value, row_number() over (partition by code order by month) rnfrom @MyTable)select a.code, a.month, a.value - b.value valuefrom Ordered ainner join Ordered b on a.code = b.code and a.rn = b.rn + 1[/CODE]=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-13 : 00:38:16
|
[code]SELECT t.code, t.month, t.value - t1.value AS valueFROM table tCROSS APPLY (SELECT TOP 1 value FROM table WHERE month < t.month ORDER BY month DESC)t1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-03-13 : 14:47:12
|
Thanks both, will try when I'm next in work. |
|
|
|
|
|
|
|