Netting between +ve and -ve rows

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

Netting between +ve and -ve rows

Author

Topic

vaibhavi_c
Starting Member

1 Post

Posted - 2010-07-29 : 14:43:47

I have 4 rows in a table. Some are +ve and some are -ve.

Example :
+1000
+200
-500
-800

I want to do netting between +ve and -ve rows but not with their sum.

For eg : First +ve row +1000 and First -ve row 500 will give me 500 in first row and 0 in third row and the data will look like this.

+500
+200
0
-800

After this +500 will be subtracted from -800 to give me this.

0
+200
0
-300

And Finally +200 will be substracted from -300 to give me this final output.

0
0
0
-100

Can someone help me on how to write this with help of cursor(or any other way in SQL).

Sachin.Nand

2937 Posts

Posted - 2010-07-30 : 00:55:34

Why you want to all that?
Doesnt (1000+200-500-800) give -100 ??


Select sum(yourcolumn) from yourtable

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 01:00:34

will this works

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-30 : 01:02:13

quote:
Originally posted by kashyap_sql

will this works

With Regards
Kashyap M

What wont work?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 01:07:23

let me try for the above post by temp table

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 01:13:45

quote:
Originally posted by Idera

Why you want to all that?
Doesnt (1000+200-500-800) give -100 ??

Select sum(yourcolumn) from yourtable

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

by this just giving the sum the poll is that out must be 0
0
0
-100
the result just a sum not getting the output

With Regards
Kashyap M

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-30 : 01:15:14

[code]-- Sample Table
declare @tbl table
(
val int
)

-- Sample Data
insert into @tbl
select 1000 union all
select 200 union all
select -500 union all
select -800

-- The Query
; with result as
(
select *, row_no = row_number() over (order by val)
from @tbl
)
select result = case when row_no = 1 then sum(val) over() else 0 end
from result
order by row_no desc
/*
result
-----------
0
0
0
-100

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 01:39:44

[code] (4 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name 'result'.
[/code]
i am getting this error

With Regards
Kashyap M

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 02:57:26

quote:
Originally posted by kashyap_sql

(4 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name 'result'.

i am getting this error

With Regards
Kashyap M

Execute the script as a hole.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 03:25:29

yes got it ;-)

With Regards
Kashyap M

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-30 : 04:23:15

vaibhavi_c = kashyap_sql ?

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 04:33:05

quote:
Originally posted by khtan

vaibhavi_c = kashyap_sql ?

KH
[spoiler]Time is always against us[/spoiler]

might be

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-07-30 : 04:35:01

quote:
Originally posted by senthil_nagore

quote:
Originally posted by kashyap_sql

(4 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name 'result'.

i am getting this error

With Regards
Kashyap M

Execute the script as a hole.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hole ???????

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-30 : 04:40:12

quote:
Originally posted by Idera

quote:
Originally posted by senthil_nagore

quote:
Originally posted by kashyap_sql

(4 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name 'result'.

i am getting this error

With Regards
Kashyap M

Execute the script as a hole.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hole ???????

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I mean that,Execute the full script as a single batch

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-30 : 06:31:18

quote:
Originally posted by senthil_nagore

quote:
Originally posted by khtan

vaibhavi_c = kashyap_sql ?

KH
[spoiler]Time is always against us[/spoiler]

might be

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

No i just involved in the poll that's it

With Regards
Kashyap M

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 04:28:41

quote:
Originally posted by Idera

quote:
Originally posted by senthil_nagore

quote:
Originally posted by kashyap_sql

(4 row(s) affected)
Msg 208, Level 16, State 1, Line 4
Invalid object name 'result'.

i am getting this error

With Regards
Kashyap M

Execute the script as a hole.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hole ???????

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I guess he must have meant "whole"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources