Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 - Query Calculation help

Posted on 2013-12-27
4
Medium Priority
?
962 Views
Last Modified: 2013-12-27
I need help in writing a SQL query which does the following :

My SQL query is based on sample input data attached(Capture.PNG) with this email.

I want to do the following calculations:

1. Group By Shift and EmployeeId.

2.  Days Worked by each employee.

3. Total Joints = Sum of all joints done by each employee.

4. Decay Joints/Day   = Total Joints /Days Worked.

5. Decay Good =  All DecayPassResult with 'Pass'.

6. Decay Total=  All DecayPassResult with 'Pass' and 'No Pass'(Empty).

7. Decay Pass % = Decay Good / Decay Total * 100.

5. Chamber Good =  All ChamberPassResult with Pass.

6. Chamber Total=  All ChamberPassResult with 'Pass' and 'No Pass'(Empty).

7. Chamber Pass % = Chamber Good / Chamber Total * 100.


I came up with this query below, but my results are no good...

Select       
      Shift as 'Shift',
      EmployeeName as 'Employee Name',            
      Count(Distinct DateOnly) as 'Days Worked',
      Sum (Joints) as 'Total Joints',
      Sum (Joints) / Count(Distinct DateOnly) as 'Decay Joints/Day',            
      (Select Count(*) from @StageDetails Where DecayPassResult = 'Pass' ) as 'Decay Good' ,
      (Select Count(*) from @StageDetails ) as 'Decay Total' ,
      Convert(Varchar(20),(Select Count(*) from @StageDetails Where DecayPassResult = 'Pass')) + '/' + Convert(Varchar(20), (Select Count(*) from @StageDetails)) as 'Decay Good/Total',
      ((Select Count(*) from @StageDetails Where DecayPassResult = 'Pass') * 100 /(Select Count(*) from @StageDetails))  as 'Decay Pass%',      
      (Select Count(*) from @StageDetails Where ChamberPassResult = 'Pass')  as 'Chamber Good',
      (Select Count(*) from @StageDetails)  as 'Chamber Total',
      Convert(Varchar(20),(Select Count(*) from @StageDetails Where ChamberPassResult = 'Pass')) + '/' + Convert(Varchar(20), (Select Count(*) from @StageDetails)) as 'Chamber Good/Total',
      ((Select Count(*) from @StageDetails Where ChamberPassResult = 'Pass') * 100 /(Select Count(*) from @StageDetails)) as 'Chamber Pass%'      ,
      ((Select Count(*) from @StageDetails Where ChamberPassResult = 'Pass') / Count(Distinct DateOnly)) as 'Chamber Pass/Day'
From
      @StageDetails  
Group By
      Shift, EmployeeId, EmployeeName

 

any Idea?

Thanks
Capture.PNG
0
Comment
Question by:mani_sai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39741854
Hi.

If I am understanding your requirement and data correctly, here are some suggestions to your calculations above.

1. Group By Shift and EmployeeId.
2.  Days Worked by each employee.
3. Total Joints = Sum of all joints done by each employee.
4. Decay Joints/Day   = Total Joints /Days Worked.
It appears you understand this part, but note well that SQL Server performs integer division when both the numerator and denominator are integers; therefore, your decay may be off.  To fix this, you can use CONVERT(DECIMAL(12,2), SUM(Joints))/COUNT(DISTINCT DateOnly) as you cause SQL to perform regular division.  You can then ROUND, FLOOR, or CEILING the result based on your environment/need.

5. Decay Good =  All DecayPassResult with 'Pass'.
6. Decay Total=  All DecayPassResult with 'Pass' and 'No Pass'(Empty).
7. Decay Pass % = Decay Good / Decay Total * 100.
8. Chamber Good =  All ChamberPassResult with Pass.
9. Chamber Total=  All ChamberPassResult with 'Pass' and 'No Pass'(Empty).
10. Chamber Pass % = Chamber Good / Chamber Total * 100.
Did you really want this for each employee?  The current implementation gets everything, which may be why the counts are off plus the integer division I spoke of above.  Another way to fix that issue by the way is to introduce a float value like 100.0, so the following also fixes the integer division issue.

[Decay Good] * 100.0 / [Decay Total]

Open in new window


Regardless of your intention (all employees or per-employee total), I think conditional aggregates will help you perform the calculations in one GROUP BY.

Here is an example:
COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END)

Open in new window


I will wait on remainder of thoughts until you have time to review above.

Kevin
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39741902
Thanks for your detailed explanation.

I am getting closer to the results...

How to use Convert and Round so that I will get results like 99.50 instead of  just 99 etc...


Here is my modified query:

Select       
      Shift as 'Shift',
      EmployeeName as 'Employee Name',            
      Count(Distinct DateOnly) as 'Days Worked',
      Sum (Joints) as 'Total Joints',
      Sum (Joints)/Count(Distinct DateOnly) as 'Decay Joints/Day',            
      COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END) as 'Decay Good' ,
      COUNT(DecayPassResult) as 'Decay Total' ,
      Convert(Varchar(20),COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END)) + '/' + Convert(Varchar(20), COUNT(DecayPassResult)) as 'Decay Good/Total',
      (COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END) * 100 /COUNT(DecayPassResult))  as 'Decay Pass%',      
      COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END)  as 'Chamber Good',
      COUNT(ChamberPassResult)  as 'Chamber Total',
      Convert(Varchar(20),COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END)) + '/' + Convert(Varchar(20), COUNT(ChamberPassResult)) as 'Chamber Good/Total',
      (COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END) * 100 /COUNT(ChamberPassResult)) as 'Chamber Pass%'      ,
      (COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END) / Count(Distinct DateOnly)) as 'Chamber Pass/Day'
From
      @StageDetails  
Group By
      Shift, EmployeeName, EmployeeId
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39741936
You have to CONVERT one of the operands to a non-integer data type.  With the 100 constant in the mix, it is simple to do the following:

COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END) * 100.0 /COUNT(DecayPassResult)

You can then use ROUND(..., 2) to give you two-decimal precision on the result.  Moreover, you can use CONVERT() to control the data type.

Hence, here is one solution:
CONVERT(DECIMAL(12,2), COUNT(CASE DecayPassResult WHEN 'Pass' THEN 1 END) * 100.0 / COUNT(DecayPassResult))

Open in new window

0
 
LVL 8

Author Comment

by:mani_sai
ID: 39742068
Thanks for your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

704 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