Making a negative value 'zero' and sorting

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 2005 Forums

Transact-SQL (2005)

Making a negative value 'zero' and sorting

Author

Topic

econrad
Starting Member

6 Posts

Posted - 2011-06-21 : 10:42:28

I have a query that looks at the Ending AR Balance of each customer, and if Balance <> 0, it displays CURRENT, 40-59Days, 60-89Days, 90-119Days, and 120+Days (all in descending order). Some of the data in these columns is negative, but I need to sort it as if it were ZERO, while keeping the negative values.

Any idea how to do this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 10:59:35

Show what you have so far. Sorting on columns that contain signed values will do what you want by default. Unless I'm misunderstanding your requirement.

econrad
Starting Member

6 Posts

Posted - 2011-06-21 : 11:04:36

This is what I have so far:
SELECT TOP (100) PERCENT dbo.RM00101.CUSTNMBR AS Cust#, dbo.RM00101.CUSTNAME AS CustName, dbo.RM00103.CUSTBLNC,
dbo.RM00103.AGPERAMT_1 AS CurrentAmt, dbo.RM00103.AGPERAMT_2 AS [40-59Days], dbo.RM00103.AGPERAMT_3 AS [60-89Days],
dbo.RM00103.AGPERAMT_4 AS [90-119Days], dbo.RM00103.AGPERAMT_5 AS [120+Days], dbo.AR_Previous_Weeks_Totals.Balance,
dbo.AR_Previous_Weeks_Totals.[Current], dbo.AR_Previous_Weeks_Totals.[40], dbo.AR_Previous_Weeks_Totals.[60],
dbo.AR_Previous_Weeks_Totals.[90], dbo.AR_Previous_Weeks_Totals.[120]
FROM dbo.RM00101 RIGHT OUTER JOIN
dbo.RM00103 ON dbo.RM00101.CUSTNMBR = dbo.RM00103.CUSTNMBR CROSS JOIN
dbo.AR_Previous_Weeks_Totals
WHERE (dbo.RM00103.CUSTBLNC <> 0) AND (NOT (dbo.RM00101.CUSTNMBR IN ('S00000', 'S00500', 'S00750', 'S00700', 'SECI SERVICE', 'R99999')))
ORDER BY [120+Days] DESC, [90-119Days] DESC, [60-89Days] DESC, [40-59Days] DESC, CurrentAmt DESC

But if there is a negative balance in 120+ column I need to to treat it as a ZERO, but still show the negative balance in the results

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-21 : 11:05:55

This does what I think you're saying... but not sure how it's useful.

Pay close attention to the Order By clause


Declare @t table (
	id int identity(1,1),
	col1 int,
	col2 int,
	col3 int
)

While ((Select COUNT(*) From @t) < 500)
	Insert Into @t 
	Select 
		RAND(CHECKSUM(NEWID())%100000000)*500 - RAND(CHECKSUM(NEWID())%100000000)*250,
		RAND(CHECKSUM(NEWID())%100000000)*500 - RAND(CHECKSUM(NEWID())%100000000)*250,
		RAND(CHECKSUM(NEWID())%100000000)*500 - RAND(CHECKSUM(NEWID())%100000000)*250

Select * 
From @t
Order By isnull(nullif(sign(col1),-1),0)*col1 Desc, isnull(nullif(sign(col2),-1),0)*col2 Desc, isnull(nullif(sign(col3),-1),0)*col3 Desc

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-21 : 11:13:20

Here's a slightly shorter version of Corey's solution:

Select * From @t
Order By (ABS(col1)+col1)/2 Desc, (ABS(col2)+col2)/2 Desc, (ABS(col3)+col3)/2 Desc

edit: Come to think of it, you can reduce that still further:

Select * From @t
Order By ABS(col1)+col1 Desc, ABS(col2)+col2 Desc, ABS(col3)+col3 Desc

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 11:32:44

Ahhh, I see. So the second sort predicate will treat the results as if all of the negatives in the 1st were the same.

Wasn't understanding.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-21 : 12:47:58

Always got to show me up

I just like to use sign()... when else would I get to

quote:
Originally posted by robvolk

Here's a slightly shorter version of Corey's solution:

Select * From @t
Order By (ABS(col1)+col1)/2 Desc, (ABS(col2)+col2)/2 Desc, (ABS(col3)+col3)/2 Desc

edit: Come to think of it, you can reduce that still further:

Select * From @t
Order By ABS(col1)+col1 Desc, ABS(col2)+col2 Desc, ABS(col3)+col3 Desc

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-21 : 12:52:38

I love SIGN() too.

I love lamp.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-21 : 14:48:11

lamp.

Dare I ask?

Corey

I Has Returned!!

robvolk
Most Valuable Yak