Returns the percent column as the value 1 all over. How do I fix it?

Question

I want to create a view that calculates percentage, but what I have done, it only returns the percent column as the value 1 all over. How do I fix it?

GO
CREATE VIEW vRejectedProductsByType AS
    SELECT 
        DimProductType.ProductTypeName,
        DimProductSubtype.ProductSubtypeName,
        CONVERT(DECIMAL(5,4), ManufacturingFact.RejectedProducts * 100 / (ManufacturingFact.AcceptedProducts + ManufacturingFact.RejectedProducts)) AS PercentRejected,
        CONVERT(INT, ManufacturingFact.AcceptedProducts + ManufacturingFact.RejectedProducts) AS TotalManufactured,
        CONVERT(DATE, ManufacturingFact.DateOfManufacture) AS DateOfManufacture
    FROM DimProduct
    INNER JOIN DimProductSubtype
    ON DimProduct.ProductSubtypeCode = DimProductSubtype.ProductSubtypeCode
    INNER JOIN DimProductType
    ON DimProductType.ProductTypeCode = DimProductSubtype.ProductTypeCode
    INNER JOIN ManufacturingFact
    ON DimProduct.ProductCode = ManufacturingFact.ProductCode;
GO

Answer 1

As others commented, here is the result. First, your long table names make for more difficult readability. By using alias names that closer match the table source, it becomes easier to read. For example DimProduct now has an alias "dp". DimProductSubType is "st" (for sub type), similar with t=type, mf=manufacturing fact.

Now, you had all those converts. If the data types are already integer, no need to convert an int to an int.

As for the date, that would be the only one that I MIGHT keep the convert( date, ) context IF that column were based on a date/time inclusive and you only wanted to see the date itself, otherwise, just leave the date alone.

Your percentage calculation, since you were dividing int by an int, it gave you an int. To fix, just turn one into a decimal or float as noted by multiplying by 100.0

SELECT 
        t.ProductTypeName,
        st.ProductSubtypeName,
        mf.RejectedProducts * 100.0 / (mf.AcceptedProducts + mf.RejectedProducts) PercentRejected,
        mf.AcceptedProducts + mf.RejectedProducts TotalManufactured,
        mf.DateOfManufacture
    FROM 
        DimProduct dp
            JOIN DimProductSubtype st
                ON dp.ProductSubtypeCode = st.ProductSubtypeCode
                JOIN DimProductType t
                    ON st.ProductTypeCode = t.ProductTypeCode
            JOIN ManufacturingFact mf
                ON dp.ProductCode = mf.ProductCode;

Finally, to ever see / sample multiplication impacts in the future, you could always just sample from the SQL command window area and do something like

select 37 / 40
vs
select 37.0 / 40
or
select 37 / 40.0

and you can immediately see what SQL is going to do based on the data types.