SQL Datediff function returning minimum value of 1
Posted on 2013-05-28
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
WHERE Priority < 7 and Closed > GETDATE()-273
GROUP BY YEAR(CLOSED), DATEPART(iso_week,Closed)
ORDER BY YEAR(CLOSED), DATEPART(iso_week,Closed)