Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Formula Using DATEDIFF to determine percentage

Posted on 2005-05-13
3
Medium Priority
?
398 Views
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.

Craig
0
Comment
Question by:CraigDerington
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13998515
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

by:CraigDerington
ID: 13998585
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13998728
Np, glad it helped, and thanks for the points!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 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