Formula Using DATEDIFF to determine percentage

Posted on 2005-05-13
Last Modified: 2010-03-19
Hello EE,
I am needing to generate a report to show me a percentage where our production personnel met their commitment times.  I am using a DATEDIFF function to calculate the turn-around time, but I need to be able to divide the number of time they met the commitment by the total widgets they repaired.  So, if the number of times they met the commitment (which is 6 days) is divided by the total, I would be able to produce a production commitment %.

So far, I have come up with this.  

Begin SQL Statement


SELECT DISTINCT(techName), COUNT(*) AS Total, SUM(SLA/COUNT(*) * 100) AS [SLA%]
FROM (SELECT DATEDIFF(d,DockDate,ShipDte) AS a1, SUM (CASE WHEN a1 <= 6) AS b1
          FROM dbo.UnitInfo
          WHERE ShipDte BETWEEN '4/1/2005' AND '4/30/2005'
FROM dbo.UnitInfo
GROUP BY techName


End SQL Statement

As you can clearly see, my syntax leaves a lot be be desired.  I think I need a third sub-query to get the count on the number of times the DateDiff result as a1 is less than or equal to 6, but I can figure it out.

Please help.  Much Thanks.

Question by:CraigDerington
    LVL 68

    Accepted Solution

    I think something like this will do it:

    SELECT techName, COUNT(*) AS Total,
          CAST(SUM(CASE WHEN DATEDIFF(DAY, DockDate, ShipDte) <= 6 THEN 1 ELSE 0 END) * 100.0
          / COUNT(*) AS DECIMAL(4,1)) AS [SLA%]
    FROM dbo.UnitInfo
    GROUP BY techName
    LVL 1

    Author Comment

    Many thanks.  I wish I could write the SQL like you do.  It's amazing the response time.  I was still trying to mess with it in query anyalzer by the time I got your answer.  That's awesome!

    I have accepted your answer and you are awared the points.

    Thanks again.

    LVL 68

    Expert Comment

    Np, glad it helped, and thanks for the points!

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now