Extra column with difference

Question

Extra column with difference

kishoremania

Ten Centuries

Points: 1059
More actions
August 27, 2012 at 11:46 am

#263351

Hi,
I am giving you sample data how it is
INSERT INTO Test (Year,Quarter,Group,Amount) VALUES
('2011', '1', 'G1','10'),
('2011', '2', 'G1','25'),
('2011', '3', 'G1','35'),
('2011', '4', 'G1','50'),
('2012', '1', 'G1','75'),
('2012', '2', 'G1','80'),
('2011', '1', 'G2','5'),
('2011', '2', 'G2','20'),
('2011', '3', 'G2','45'),
('2011', '4', 'G2','65'),
('2012', '1', 'G2','75'),
('2012', '2', 'G2','90')
I need an output with extra column DiffAmount, which is the difference of amount between current quarter and previous quarter.
The expected output should look like
YearQuarterGroupAmountDiffAmount
2011 1 G1 10 0
2011 2 G1 25 15
2011 3 G1 35 10
2011 4 G1 50 15
20121G17525
20122G1805
20111G250
20112G22015
20113G24525
20114G26520
20121G27510
20122G29015
Can some one help me out with a query for it.
Thanks in Advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

A few words of caution: you should REALLY avoid using reserved words as column names. 3 out of 4 of your columns are reserved, which tends to really confuse the syntax checker.

;with datecte as (

select datediff (quarter,0,dateadd(quarter,[quarter]-1,dateadd(year,[year]-1900,0))) dateseq, * from #test)

select d1.*,case when d2.amount is null then 0 else d1.amount-d2.amount end diff

from datecte d1

left join datecte d2 on d1.[group]=d2.[group] and d1.dateseq=d2.dateseq+1

order by [group], dateseq

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

A simple modification to the code to avoid problems with missing quarters (shouldn't happen but it could), I also believe is easier to understand.

DECLARE @test-2 TABLE( Year int,Qrtr int, GroupName char(2),Amount int)

INSERT INTO @test-2 (Year,Qrtr,GroupName,Amount) VALUES

('2011', '1', 'G1','10'),

('2011', '2', 'G1','25'),

('2011', '3', 'G1','35'),

('2011', '4', 'G1','50'),

('2012', '1', 'G1','75'),

('2012', '3', 'G1','80'),

('2011', '1', 'G2','5'),

('2011', '2', 'G2','20'),

('2011', '3', 'G2','45'),

('2011', '4', 'G2','65'),

('2012', '1', 'G2','75'),

('2012', '2', 'G2','90');

with datecte as (

SELECT *,

ROW_NUMBER() OVER ( PARTITION BY GroupName ORDER BY year, Qrtr) dateseq

from @test-2)

select d1.*,case when d2.amount is null then 0 else d1.amount-d2.amount end diff

from datecte d1

left join datecte d2 on d1.GroupName=d2.GroupName and d1.dateseq=d2.dateseq+1

order by d1.GroupName, d1.dateseq

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kishoremania Ten Centuries Points: 1059 More actions · Answer 3

Thank you Matt, Luis

It saved lot of my time.

I am sorry for giving reserved columns. Those are not my real columns. I have large number of columns, just to make easily understand I put these columns.

I appreciate your time for it.