Formula Using DATEDIFF to determine percentage
Posted on 2005-05-13
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
WHERE ShipDte BETWEEN '4/1/2005' AND '4/30/2005'
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.