View Full Version : removing negative numbers

2-Aug-2013, 04:27 PM
I did a datediff and called the result, Received CT. I do not want any negative values. I have tried several ways to remove the negative values. I went to the conditions tab and put >0 or >=0 in the received CT field and put a formula in at the bottom of the conditions tab and I always got an error message. THe attached pdf gives the details

TIA for your help

Bob Cergol
3-Aug-2013, 11:28 AM
Here are two ways to do this: using the ABS (absolute) function, or a case statement.
You'd of course replace the date constants with your table.date_columns.
You can cut and paste this into a new Dynamic SQL View to execute and see the results.


-- show results of two datediff scenarios
Datediff(dy,'2013-11-01','2013-11-04') as PositiveDiff,
Datediff(dy,'2013-11-04','2013-11-01') as NegativeDiff,

-- showing formula using absolute function to return zero for negative datediff values
(ABS(Datediff(dy,'2013-11-01','2013-11-04')) + Datediff(dy,'2013-11-01','2013-11-04')) / 2 as r1,
(ABS(Datediff(dy,'2013-11-04','2013-11-01')) + Datediff(dy,'2013-11-04','2013-11-01')) / 2 as r2,

-- showing use of case statement to return zero for negative datediff values
case when Datediff(dy,'2013-11-01','2013-11-04') > 0 then Datediff(dy,'2013-11-01','2013-11-04') else 0 end as r3,
case when Datediff(dy,'2013-11-04','2013-11-01') > 0 then Datediff(dy,'2013-11-04','2013-11-01') else 0 end as r4