Formula Using DATEDIFF to determine percentage

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.

Craig
LVL 1
CraigDeringtonAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
0
 
CraigDeringtonAuthor Commented:
Scott,
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.

Craig
0
 
Scott PletcherSenior DBACommented:
Np, glad it helped, and thanks for the points!
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.

All Courses

From novice to tech pro — start learning today.