SQL - 如何从datediff函数中排除负值

How do I exclude all datediff values that are negative from my average statement?

如何从我的平均声明中排除所有的负数值？

query:

查询：

select Avg_DayDiff case when Avg_DayDiff > 0

from(

SELECT DATEDIFF(DAY, xx, yy) AS Avg_DayDiff

FROM Database1.dbo.tbl_1

where  
        month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
       and ApprovalRequiredFrom = 'GRM'

       ) temptable

My query returns a syntax error from my first line (select statement) I am still learning so any tips are greatly appreciated!

我的查询从我的第一行（select语句）返回语法错误我还在学习所以任何提示都非常感谢！

the result I am looking for is to only return datediff if the # > 0, so then I can average it.

我正在寻找的结果是只有＃> 0才返回datediff，所以我可以平均它。

thanks

谢谢

3 个解决方案

#1

Please try to use a where clause for your main query instead of case when in the select statement:

在select语句中，请尝试使用where子句作为主查询而不是case：

select Avg_DayDiff 
from(
      SELECT DATEDIFF(DAY, xx, yy) AS Avg_DayDiff
      FROM Database1.dbo.tbl_1
      where  
       month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
       and ApprovalRequiredFrom = 'GRM'
) temptable
where Avg_DayDiff > 0

#2

You could simply exclude the value in where eg

您可以简单地将值排除在例如

    SELECT DATEDIFF(DAY, xx, yy) AS Avg_DayDiff
    FROM Database1.dbo.tbl_1
    where  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
        and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
           and ApprovalRequiredFrom = 'GRM'
            and  DATEDIFF(DAY, xx, yy) > 0

#3

Use abs() function which returns only positive values.

使用abs（）函数只返回正值。

SELECT abs(DATEDIFF(DAY, xx, yy)) AS Avg_DayDiff

The above will be enough to obtain positive values and avoid negative values.

以上将足以获得正值并避免负值。

#1