Average without negative numbers ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Average without negative numbers ?

Author

Topic

atoutsweb
Starting Member

3 Posts

Posted - 2009-04-07 : 11:11:16

Hi guys,

I can't find a solution for a query that consider negative numbers as 0. My query is :

SELECT AVG(CAST(DATEDIFF(day, t1.date_1, t1.date_2) AS int)) AS nbJourMoyenne
FROM t1 ..............

But sometime datediff give back a negative number (normal) and I need to convert negative number to 0.

Example :

2 => 2
-2 => 0

AVG = 2

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:20:48

use ABS()


SELECT AVG(ABS(DATEDIFF(day, t1.date_1, t1.date_2))) AS nbJourMoyenne
FROM t1 ..............

also no need of cast as datediff's return type is int

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 11:32:53

SELECT AVG(CASE WHEN DATEDIFF(day, t1.date_1, t1.date_2)<0 THEN 0 ELSE DATEDIFF(day, t1.date_1, t1.date_2) END ) AS nbJourMoyenne
FROM t1 ..............

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 11:34:22

quote:
Originally posted by visakh16

use ABS()

SELECT AVG(ABS(DATEDIFF(day, t1.date_1, t1.date_2))) AS nbJourMoyenne
FROM t1 ..............

also no need of cast as datediff's return type is int

No ABS() wont make negative numbers to zero. It simply make numbers to be positive

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 11:37:49

<<
2 => 2
-2 => 0

AVG = 2
>>

Simply do

SELECT AVG(DATEDIFF(day, t1.date_1, t1.date_2)) AS nbJourMoyenne
FROM t1 ..............
WHERE t1.date_1<=t1.date_2

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:44:21

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

use ABS()

SELECT AVG(ABS(DATEDIFF(day, t1.date_1, t1.date_2))) AS nbJourMoyenne
FROM t1 ..............

also no need of cast as datediff's return type is int

No ABS() wont make negative numbers to zero. It simply make numbers to be positive

Madhivanan

Failing to plan is Planning to fail

ah... sorry i missed that part...

thought he wants avg of all regardless of sign

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-07 : 11:46:21

quote:

2 => 2
-2 => 0

AVG = 2

won't the average of (2,0) return 1? Are you sure you don't want negative numbers to be NULL instead of 0?

But Madhi's solution (above) will not consider negative numbers (ie: treat them like NULL). But if you really want them to be 0 - so avg of (0,2)=1 - then that needs a different solution.

Be One with the Optimizer
TG

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-07 : 11:51:47

why is there negative numbers coming in the first place? what is it you are trying to get to in the first place. JourMoyenne is average day something is taking I assume. So why are there negative numbers coming in. Pourquoi?
Is your data clean?
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

atoutsweb
Starting Member

3 Posts

Posted - 2009-04-08 : 02:26:51

Sorry for the mistake, the objective was :

2 => 2
-2 => 0

AVG = 1

So I think the solution is "CASE WHEN DATEDIFF(day, t1), I'm going to try right now.

Merci.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 02:30:25

Remember that zero days are included in the average value.
Maybe OP wants negative days to be calculated as NULL in the average?

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

atoutsweb
Starting Member

3 Posts

Posted - 2009-04-08 : 02:40:28

Hi guys, the query is amazing and is working perfectly, I didn't know the command "CASE". Negative numbers are included as zero ine the AVG and it's the goal.

The final query is :

SELECT AVG(CASE WHEN DATEDIFF(day, t1.date1, t1.date2) < 0 THEN 0 ELSE DATEDIFF(day, t1.date1, t1.date2) END) AS nbJourMoyenne from t1

All the best. Merci.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources