SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Maths on a month by month scenario?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 03/11/2013 :  16:42:04  Show Profile  Reply with Quote
I have a table.

Lets just assume for the sake of discussion it has the fields:

nominal code
month/year
value

Example entries might be

AB10000 201201 1000
AB10001 201201 5000
AB10000 201202 2000
AB10001 201202 4000

So I need some code that can calculate that the difference between 201201 and 201202 for AB10000 is -1000 (1000-2000) and the difference between 201201 and 201202 for AB10001 is 1000 (5000-4000).

Any takers? This would be an ongoing state so there would also be entries for 201203 which I'd need to compare to 201202 and so on.

Thank you for reading.

Edited by - Rasta Pickles on 03/11/2013 16:43:41

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 03/11/2013 :  17:19:08  Show Profile  Visit robvolk's Homepage  Reply with Quote
-- set up data
DECLARE @t TABLE(nominalCode VARCHAR(10) NOT NULL, monthYear DATE NOT NULL, VALUE INT NOT NULL)
INSERT @t VALUES('AB10000','20120101',1000),
('AB10001','20120101',5000),
('AB10000','20120201',2000),
('AB10001','20120201',4000)

-- process query
;WITH CTE(nominalCode, monthYear, VALUE, row_num) AS (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY nominalCode ORDER BY monthYear)
	FROM @t)
SELECT A.nominalCode, A.monthYear Start, B.monthYear Finish, A.value-B.value Diff 
FROM CTE A
INNER JOIN CTE B ON A.nominalCode=B.nominalCode AND A.row_num=B.row_num-1

-- insert new value
INSERT @t VALUES('AB10000','20120301',5000)

-- same query
;WITH CTE(nominalCode, monthYear, VALUE, row_num) AS (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY nominalCode ORDER BY monthYear)
	FROM @t)
SELECT A.nominalCode, A.monthYear Start, B.monthYear Finish, A.value-B.value Diff 
FROM CTE A
INNER JOIN CTE B ON A.nominalCode=B.nominalCode AND A.row_num=B.row_num-1
I'm using date to store month/year rather than a number or character string, and I strongly recommend you do the same. Anything resembling a date should be stored that way to prevent invalid dates from being entered and to ensure they order/sort correctly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000