Subtraction of values from consecutive rows

Question

Subtraction of values from consecutive rows

saumil1987

SSC Enthusiast

Points: 171
More actions
May 31, 2015 at 8:54 pm

#318674

I've;
Id.........|......type....|.....Value
2001................1...............20
2001................2...............32
2002................1...............19
2002................2...............21
2003................1............... 3
2003................2...............30
I want;
Id........|.......Value
2001.................12
2002..................2
2003.................27
Please help,
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Welcome to SSC! You'll find that you'll get better and quicker answers if you follow the instructions in Jeff Moden's article "How to get best help" http://www.sqlservercentral.com/articles/Best+Practices/61537/

code="sql"]use tempdb;

go

create table #Temp(

Yr INT,

SomeType TINYINT,

Value TINYINT

);

INSERT INTO #Temp(Yr,someType,Value) VALUES (2001,1,20),(2001,2,32),(2002,1,19),(2002,2,21),(2003,1,3),(2003,2,30);

SELECT Yr, SUM(Addthese) AS FinalValue

FROM (

SELECT Yr

, SomeType

, CASE WHEN SomeType = 1 THEN -1 * Value ELSE Value END AS Addthese

FROM #Temp

) x

GROUP BY Yr

ORDER BY Yr;[/code]

saumil1987 SSC Enthusiast Points: 171 More actions · Answer 2

Hi,

Highly appreciate your help. & yeah, thanks for that (posting) guidance link.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Quick suggestion that fits the posted sample data

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_DATA_012') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA_012;

CREATE TABLE dbo.TBL_SAMPLE_DATA_012

(

Id INT NOT NULL

,type INT NOT NULL

,Value INT NOT NULL

);

INSERT INTO dbo.TBL_SAMPLE_DATA_012(Id,type,Value)

VALUES

(2001,1,20)

,(2001,2,32)

,(2002,1,19)

,(2002,2,21)

,(2003,1, 3)

,(2003,2,30);

SELECT

SD.Id

,ABS(SUM(SD.Value

*(CASE

WHEN SD.type = 1 THEN -1

ELSE 1

END))) AS Value

FROM dbo.TBL_SAMPLE_DATA_012 SD

GROUP BY SD.Id;

Results

Id Value

----------- -----------

2001 12

2002 2

2003 27

pietlinden SSC Guru Points: 63503 More actions · Answer 4

That's what happens when you sleepcode... you do silly things. For some reason I totally forgot about ABS()...

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

pietlinden (6/1/2015)
That's what happens when you sleepcode... you do silly things. For some reason I totally forgot about ABS()...

I do know the fealing:-D

😎

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

I'm not sure if it's an improvement, just a fun way to save some keystrokes.

SELECT

SD.Id

,ABS(SUM(SD.Value*SIGN(Type-1.5))) AS Value

FROM dbo.TBL_SAMPLE_DATA_012 SD

GROUP BY SD.Id;

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

saumil1987 SSC Enthusiast Points: 171 More actions · Answer 7

saumil1987

SSC Enthusiast

Points: 171

June 1, 2015 at 11:29 pm

#1802217

Thanks Guys. Appreciate the help.