the positive amount is equal to the negative amoun

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

SQL Server 2000 Forums

Transact-SQL (2000)

the positive amount is equal to the negative amoun

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-08-04 : 10:30:20

I have a table look like


ID    Paid_amt            Allow_amt          Billed_amt
 1       400                  500                   600
 1       321                 1230                   987
 1      -400                 -500                  -600
 2       300                 300                    300
 2       500                 200                    100
 2       -500                -200                  -100

I would like to find out ID which has the positive amount is equal to negative amount.
The return result should looks like:


ID    Paid_amt          Allow_amt                  Billed_amt
 1       400               500                     600
 1      -400              -500                    -600
 2       500               200                     100
 2      -500              -200                    -100

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-04 : 10:34:35

Use the ABS function.

SELECT *
FROM tablename
WHERE ABS(Paid_amt)=ABS(Allow_Amt)

Raymond

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 11:48:52

I don't think that is what he is trying to do in this case...

maybe this will help:

Drop table #tempTable
Create Table #tempTable (tranId int identity(1,1) not null, id int, Paid_Amt int, Allow_Amt int, Billed_Amt int)
Insert Into #tempTable
Select 1, 400, 500, 600 union all Select 1, 321, 1230, 987 union all Select 1, -400, -500, -600
Union All Select 2, 300, 300, 300 union all Select 2, 500, 200, 100 union all Select 2, -500, -200, -100

Select * From #tempTable

Select A.*
From #tempTable as A
Inner Join #tempTable as B
On A.id = B.id
Where A.Paid_Amt + B.Paid_Amt = 0
and A.Allow_Amt + B.Allow_Amt = 0
and A.Billed_Amt + B.Billed_Amt = 0

Corey

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-04 : 11:56:22

Yeah, you are right - I misunderstood the requirement. To partially save face :) you could still use the ABS function in your code with ABS(a.Paid_Amt) = ABS(b.Paid_Amt), but what you've got would work fine.

Raymond

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 11:58:27

hehehe i know the feeling

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources