Anonymous Anonymous - 2 months ago
358 0

No description

SQL

My Code

DECLARE @Mean_Diff DECIMAL(10,2)                    --================================================
SET @Mean_Diff = (                                    -- Get the Mean Difference hours to
                  SELECT AVG(DifferenceHours)        -- Calculate Variance
                  FROM Sample_Data                  --================================================
                  )

--------------------------------------
                    
DECLARE @Variance DECIMAL(10,2)
SET @Variance = (                                                                    --================                
SELECT CAST(SUM(POWER(DifferenceHours - @Mean_Diff,2))/COUNT(*) AS DECIMAL (10,2))  --Get Variance 
FROM Sample_Data                                                                    --================
                )                                                                        
--------------------------------------
                                                    --=================================================
DECLARE @SD DECIMAL(10,2)                            -- Get Standard Deviation (Square Root of Variance)
SET @SD = SQRT(@Variance)                            --=================================================

SELECT *        
        , DifferenceHours - @Mean_Diff AS 'Deviation'    
        , CASE                                                
            WHEN ((Actual_Hours - Standard_Hours)/Standard_Hours)*100>25
                THEN          
                    CASE 
                        WHEN ABS(DifferenceHours - @Mean_Diff)<=1*@SD
                        THEN ''
            
                        WHEN ABS(DifferenceHours - @Mean_Diff) >1*@SD
                        AND ABS(DifferenceHours - @Mean_Diff) <=2*@SD
                        THEN 'Recommend Review'
                
                        WHEN ABS(DifferenceHours - @Mean_Diff) > 2*@SD
                        THEN 'Strongly Recommend Review'
                    ELSE ''
                    END
            ELSE ''
            END AS 'Recommendation'

FROM Sample_Data