troubleshooting Question

SQL Server 2005 - Query Calculation help

Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
4 Comments1 Solution1023 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros