• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

SQL Datediff function returning minimum value of 1

So I am tracking the average number of days our troublecall tickets are open. Now with the following code i can get close, but my problem that I found is that if a ticket is opened and closed on the same day the Datediff function counts it as 0 days open instead of 1. Is there a better function to use or is there a way to have datediff fulfill the job I need it to do?

Here is the code:

SELECT      DATEPART(iso_week,Closed)as 'ISO Week'
      , AVG(Datediff(DAY,Created,Closed))as 'Avg days to Close'       
      , COUNT(closed)as Closed
FROM TSL
WHERE Priority < 7 and Closed > GETDATE()-273
GROUP BY YEAR(CLOSED), DATEPART(iso_week,Closed)
ORDER BY YEAR(CLOSED), DATEPART(iso_week,Closed)
0
NCSA SCADA
Asked:
NCSA SCADA
1 Solution
 
ThomasianCommented:
, AVG(CASE WHEN Datediff(DAY,Created,Closed)=0 THEN 1 ELSE Datediff(DAY,Created,Closed) END)as 'Avg days to Close'
0
 
NCSA SCADAAuthor Commented:
Thanks for the quick response. that makes total sense now that i see the code. you saved me alot of time
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now