Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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