SQLTeam.com | Weblogs | Forums

Difference in two rows of a same table


#1

Respected Techie,

May anyone please help me on this Please.

/*
For a same month,smc,sg and spn , need to calculate the difference between NPS of year 2016 and NPS of year 2015.
the result called as SGR .
similarly
For a same month,smc,sg and spn , need to calculate the difference between RBP of year 2016 and RBP of year 2015.
the result called as RBP

DECLARE @T1 TABLE
(

YEAR varchar (10),
MONTH varchar (10),
SMC varchar (10),
SG varchar (10),
SPN numeric (5,0) ,
SGR VARCHAR (50) ,
NPS float,
PGR VARCHAR (50) ,
RBP float
)
insert @t1

SELECT '2015', '1' ,'01', 'A' ,'357', '1', '319687.28', '1','78422.14' UNION ALL
SELECT '2016', '1' ,'01', 'A' ,'357', '', '350803.45', '', '85650.9' UNION ALL
SELECT '2015', '9' ,'05', 'B' ,'450', '1', '499077.64', '1','121209.59' UNION ALL
SELECT '2016', '9','05', 'B' ,'450', '', '482126.71', '' ,'117342.87'

SELECT * FROM @T1

'2015', '1' ,'01', 'A' ,'357', '31116.17', '319687.28', '1','78422.14'
'2016', '1' ,'01', 'A' ,'357', '31116.17','350803.45', '7228.76', '85650.9'
'2015', '9' ,'05', 'B' ,'450', '-16950.93','499077.64', '1','121209.59'
'2016', '9','05', 'B' ,'450', '-16950.93','482126.71','-3866.72' ,'117342.87'

Thank You Very Much.


#2

Something like this?

UPDATE t1
SET      sgr = cast(t1.RBP - t2.RBP AS VARCHAR(50))
	,pgr = cast(t1.nps - t2.nps AS VARCHAR(50))
-- SELECT t1.RBP - t2.RBP as diffrbp, t1.nps - t2.nps as diffnps -- for testing
FROM @t1 t1
INNER JOIN @t1 t2 ON t1.year = t2.year + 1
	AND t1.month = t2.month
	AND t1.smc = t2.smc
	AND t1.sg = t2.sg
	AND t1.spn = t2.spn

#3

i am extremely sorry GBritton. i have edited the post. actually i need to called the difference column as SGR and PGR.
Please suggest.
Thanks a lot.