Solved

# Formula Using DATEDIFF to determine percentage

Posted on 2005-05-13
354 Views
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.

Craig
0
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
0

LVL 1

Author Comment

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!

Thanks again.

Craig
0

LVL 68

Expert Comment

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

## Featured Post

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 (http://msdn.microsoft.com/en-us/library/ms188664.aspx) 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.