asked on SQL Server 2005 - Query Calculation help
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),(Selec
((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),(Selec
((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
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),(Selec
((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),(Selec
((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
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
Mani Pazhana
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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:
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))
ASKER
Thanks for your help.
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(
(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(
(COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END) * 100 /COUNT(ChamberPassResult))
(COUNT(CASE ChamberPassResult WHEN 'Pass' THEN 1 END) / Count(Distinct DateOnly)) as 'Chamber Pass/Day'
From
@StageDetails
Group By
Shift, EmployeeName, EmployeeId