Comparing rows within the same table

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

Comparing rows within the same table

Author

Topic

Gigi
Starting Member

23 Posts

Posted - 2009-05-26 : 14:36:38

I have a table structure something like this

ACT JNC QTY INV NET
ABC NY +5 ABCINV 0
ABC NY 0 ABCINV 145

Each record has two rows. If QTY is positive then the NET column in zero and in the very next line we have the quantity as zero for the NET amount. If the QTY is negative for a record then the first row for the record will have zero for QTY but will have a NET amount and the second row will have the QTY wil zero NET amount. What I want to do is compare ACT and the INV colums and if they match then combine the rows with spit it out like this

ACT JNC QTY INV NET
ABC NY +5 ABCINV 145

I have tried inner joins unsuccessfully. Any help from the gurus here would be extremely helpful. thank you for all your help in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 14:40:19

is there any other column in your table which is unique valued (may be an identity column)

Gigi
Starting Member

23 Posts

Posted - 2009-05-26 : 14:55:14

no....and thats the problem.....i can only compare first with ACT and then with INV and then update the QTY and NET columns depending whether the QTY is positive or negative

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-05-27 : 02:35:09

I'm not exactly getting what you're after, but if you have the rules then
select t1.act,t1.qty,t2.net ..etc from
table as t1
inner join
table as t2
on
((t1.act=t2.act and t1.inv=t2.inv) and ((t1.qty>0 and t2.net<>0) or (t1.qty<0 and t2.qty=0 and net=0 ... etc..)

To be honest, I could not really understand your question.
Don't forget there is no 'next row' so you have to code it like above.
Hope this helps a bit

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-27 : 04:18:22

Are you using sql server 2005? if so you can use ROW_NUMBER() to get a surrogate identity column.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 05:06:05

Wouldn't this do?

SELECT ACT, JNC, SUM(QTY) AS Qty, INV, SUM(NET) AS Net FROM Table1 GROUP BY ACT, JNC, INV

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 09:51:50

quote:
Originally posted by Gigi

no....and thats the problem.....i can only compare first with ACT and then with INV and then update the QTY and NET columns depending whether the QTY is positive or negative

first based on what? there's no concept of first and last in a sql table unless you specify order by means of a order by clause

Gigi
Starting Member

23 Posts

Posted - 2009-05-27 : 10:31:53

Thank you all for your replies. And I apologies if I wasnt very clear initially. I will try to answer all the questions and will try to be as precise as possible this time. Let me start from the beginning with 2-3 examples

I have a table structure something like this

ACT JNC QTY INV NET
ABC NY +5 ABCINV 0
ABC NY 0 ABCINV -145
DEF NJ 0 DEFINV +356
DEF NJ -15DEFINV 0
GHI CT +6 GHIINV 0
GHI CT 0 GHIINV -567
JKL MD 0 JKLINV +35
JKL MD -81JKLINV 0
Each record has two rows. If QTY is a positive number then the NET column in zero and in the very next line we have the quantity as zero and the NET amount is negative for the same record. On the other hand, if the QTY is negative for a record then the first row for the record will have zero for QTY but will have a NET amount as positive and the second row will have the QTY (some negative number)and the NET amount will be zero in this case. What I want to do is compare ACT and the INV colums and if they match then combine the rows with spit it out like this

ACT JNC QTY INV NET
ABC NY +5 ABCINV +145
DEF NJ -15 DEFINV +356
GHI CT +6 GHIINV -567
JKL MD -81 JKLINV +35

The only thing I can compare first against the ACT column and then against the INV column if they are the same....Hope this is clearer....and again thank you all for all your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 11:19:57

Did you try the suggestion made 05/27/2009 : 05:06:05 ?

E 12°55'05.63"
N 56°04'39.26"

Gigi
Starting Member

23 Posts

Posted - 2009-05-27 : 12:23:21

Peso,
I tried the above again and it worked this time. I must be doing something worng the first time I tried it. So we are all good. Thank you for your help

and I wanted to thank all of you who have replied here. You guys have been tremendous help. thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources