Maths on a month by month scenario? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Maths on a month by month scenario?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-11 : 16:42:04

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-11 : 17:19:08

[code]-- 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[/code]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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources