Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Datediff function returning minimum value of 1

Posted on 2013-05-28
Medium Priority
Last Modified: 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
Question by:NCSA SCADA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Accepted Solution

Thomasian earned 2000 total points
ID: 39201891
, AVG(CASE WHEN Datediff(DAY,Created,Closed)=0 THEN 1 ELSE Datediff(DAY,Created,Closed) END)as 'Avg days to Close'

Author Closing Comment

ID: 39202002
Thanks for the quick response. that makes total sense now that i see the code. you saved me alot of time

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question