Solved

SQL Server 2008 - Query Calculation Issue

Posted on 2013-12-18
10
322 Views
Last Modified: 2013-12-18
I am running this query below on SQL Server 2008:

Select Distinct
      EI.FIRSTNAME + ' ' + EI.LASTNAME as 'Employee Name',
      EI.NUM as 'Employee Id',
      EI.SHIFT as 'Shift',
      Count(Distinct CONVERT(VARCHAR(10),Date_Time,111)) as 'Days Worked',
      Count(Distinct TR.Serial_Number) as 'Total Coils Processed',
      PC.PassCoilCount as 'Chamber Coils Good',      
      PC.PassCoilCount / Count(Distinct TR.Serial_Number) * 100 as 'Chamber Coils Pass %',
      Count(Distinct TR.Serial_Number) / Count(Distinct CONVERT(VARCHAR(10),TR.Date_Time,111)) as 'Chamber Avg Coils/Day'
FROM
      AIO_Test_Results.dbo.Test_Results TR JOIN AIO_Test_Results.dbo.EmployeeInfo EI
      ON TR.Op_ID = EI.FIRSTNAME + ' ' + EI.LASTNAME
      JOIN @PassedCoils PC ON  PC.EmployeeName = TR.Op_ID
Where
      (Date_Time BETWEEN '2013-11-10 16:32:35.757' AND '2013-12-17 16:32:35.757') AND
      Len(Serial_Number) >=15 AND (System_ID LIKE '%Chamber%')
      AND EI.NUM IN (SELECT * FROM dbo.fn_SplitDelimiterString(@EmployeeList, ','))
Group By
      Shift, EI.FIRSTNAME + ' ' + EI.LASTNAME, EI.NUM  ,PC.PassCoilCount
Order By
      Shift, EI.FIRSTNAME + ' ' + EI.LASTNAME


-----------------------------------------------

Chamber Coils Pass % - is returning  0

any idea why it is not showing actual value..

Thanks
0
Comment
Question by:mani_sai
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39727041
It's possible that it's a value that rounds to 0. Try this:

CAST(PC.PassCoilCount / Count(Distinct TR.Serial_Number) * 100 as DECIMAL(10,2)) as 'Chamber Coils Pass %'
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39727045
If both columns are integers, which they appear to be as they are counts, then the result will be an integer.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39727056
Buttercup1 is correct.

integer / integer is 'integer math', and SQL ass-u-me's that the resulting value will also be an integer.

3 / 4 = 0, 5 / 2 = 2, you get the idea.

The way around this is to case one of the values as a data type that allows decimal values.

CAST(3 as decimal(5,2))/4 = .05, CAST(5 as decimal(5,2))/2 = 2.5
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 8

Author Comment

by:mani_sai
ID: 39727068
Thanks

CAST(PC.PassCoilCount / Count(Distinct TR.Serial_Number) * 100 as DECIMAL(10,2)) as 'Chamber Coils Pass %'

is returning 0.00

Any idea?
0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39727075
One other thing. Move your *100 like this:

CAST(PC.PassCoilCount  * 100 / Count(Distinct TR.Serial_Number) as DECIMAL(10,2)) as 'Chamber Coils Pass %'

For * and /, if no brackets specify order, they will be calculated left to right. Try these 3 scenarios, and choose the one that works best for you. I recommend the last one.

SELECT CAST(1/8*100 AS DECIMAL(10,2))

SELECT CAST(1*100/8 AS DECIMAL(10,2))

SELECT CAST(CAST(1 AS DECIMAL(10,2))*100/CAST(8 AS DECIMAL(10,2)) AS DECIMAL(10,2))

Open in new window

0
 
LVL 8

Author Comment

by:mani_sai
ID: 39727077
Here is my Full Code:

DECLARE @PassedCoils TABLE(EmployeeId Varchar(30) NOT NULL, EmployeeName Varchar(50) NOT NULL, PassCoilCount int NOT NULL);


INSERT INTO @PassedCoils
Select Distinct
      EI.NUM,
      EI.FIRSTNAME + ' ' + EI.LASTNAME ,
      Count(Distinct Serial_Number)
FROM
      AIO_Test_Results.dbo.Test_Results TR JOIN AIO_Test_Results.dbo.EmployeeInfo EI
ON
      TR.Op_ID = (EI.FIRSTNAME + ' ' + EI.LASTNAME)
Where
      (TR.Date_Time BETWEEN '2013-11-10 16:32:35.757' AND '2013-12-17 16:32:35.757') AND
      Len(TR.Serial_Number) >=15 AND (TR.System_ID LIKE '%Chamber%') AND (TR.Test_Result LIKE '%PASS%')
      AND EI.NUM IN (SELECT * FROM dbo.fn_SplitDelimiterString(@EmployeeList, ','))
Group By
      EI.FIRSTNAME + ' ' + EI.LASTNAME, Shift, EI.NUM
      
 

--Final results for Chamber
Select Distinct
      EI.FIRSTNAME + ' ' + EI.LASTNAME as 'Employee Name',
      EI.NUM as 'Employee Id',
      EI.SHIFT as 'Shift',
      Count(Distinct CONVERT(VARCHAR(10),Date_Time,111)) as 'Days Worked',
      Count(Distinct TR.Serial_Number) as 'Total Coils Processed',
      PC.PassCoilCount as 'Chamber Coils Good',      
      CAST(PC.PassCoilCount / Count(Distinct TR.Serial_Number) * 100 as DECIMAL(10,2)) as 'Chamber Coils Pass %' ,
      Count(Distinct TR.Serial_Number) / Count(Distinct CONVERT(VARCHAR(10),TR.Date_Time,111)) as 'Chamber Avg Coils/Day'
FROM
      AIO_Test_Results.dbo.Test_Results TR JOIN AIO_Test_Results.dbo.EmployeeInfo EI
      ON TR.Op_ID = EI.FIRSTNAME + ' ' + EI.LASTNAME
      JOIN @PassedCoils PC ON  PC.EmployeeName = TR.Op_ID
Where
      (Date_Time BETWEEN '2013-11-10 16:32:35.757' AND '2013-12-17 16:32:35.757') AND
      Len(Serial_Number) >=15 AND (System_ID LIKE '%Chamber%')
      AND EI.NUM IN (SELECT * FROM dbo.fn_SplitDelimiterString(@EmployeeList, ','))
Group By
      Shift, EI.FIRSTNAME + ' ' + EI.LASTNAME, EI.NUM, PC.PassCoilCount
Order By
      Shift, EI.FIRSTNAME + ' ' + EI.LASTNAME
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39727093
See my last post.
0
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39727097
Thanks. It worked.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39727101
<Slight correction to the T-SQL in the first comment>
CAST one of the two amounts in the division, not the entire amount.
-- These returns the decimal places
SELECT CAST(3 as decimal(5,2))/4
SELECT 3/CAST(4 as decimal(5,2))

-- This doesn't
SELECT CAST(3/4 as decimal(5,2))

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39727106
Have you tried:

CAST(PC.PassCoilCount as DECIMAL(10,2)) / Count(Distinct TR.Serial_Number) * 100  as 'Chamber Coils Pass %'
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

730 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