[dba-Tech] Calculating Six Sigma - Please critique - Mathematicianswhere are you

It has been years since I have worked with these variable sets. Given a
quick look they look like they should do the task. I will assume that you
have tested the results against a series of calculations that you already
have known values.

This UFD calculation asks the question; is this used a lot by the clients
and is there a noticeable performance impact on high volumes as this type of
math calculations are not really MS SQL forte. I might be more inclined call
an external routine...

Maybe you should use descriptive names if you will ever have to go back into
the code months later.
 
It does seem that you are working on some interesting projects.

Jim

 

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Mark Breen
Sent: Thursday, September 30, 2010 5:02 AM
To: Discussion of Hardware and Software issues
Subject: [dba-Tech] Calculating Six Sigma - Please critique -
Mathematicianswhere are you

Hello All,

I have to calculate a Six Sigma value and I have written a function for SQL
Server to calculate it.

>From what I have read, Six Sigma should really be looked up against a set
of
values but I also think that it can be approximated using the formula at the
following link http://www.symphonytech.com/articles/sixsigma.htm.

The calculation is credited to Schmidt and Launsby 1997


Can I ask your assistance for the following

1) Are the calculations correct?
2) Would you rename the variables to more appropriate values that are still
readable by non-math experts

Here is the short version and then below I have also pasted my complete
function.  X, Y, and Z are the three static values in the Schmidt and
Launsby formula.


SET @X = 0.8406
SET @Y = 29.37
SET @Z = 2.221

SET @DPM=  1000000 / @OrderQty * @DefectQty

SET @NaturalLogOfDPM= log(@DPM)
SET @NaturalLogOfDPMTimesZ = @NaturalLogOfDPM * @Z
SET @CalculatedValueForSQR = @Y - @NaturalLogOfDPMTimesZ
SET @SQROfCalculatedValue = SQRT(@CalculatedValueForSQR)


SET @Result = @X + @SQROfCalculatedValue



Thanks
Mark

Here is the entire SQL Server udf



Create FUNCTION ufn_MB_CalcSixSigma
(
    @OrderQty NUMERIC(12,4),
    @DefectQty NUMERIC(12,4)
)
RETURNS NUMERIC(12,4)
AS
BEGIN

/*
The calculation is credited to Schmidt and Launsby 1997

*/


    DECLARE @DPM NUMERIC(12,4)

    DECLARE @X NUMERIC(12,4)
    DECLARE @Y NUMERIC(12,4)
    DECLARE @Z NUMERIC(12,4)

    DECLARE @NaturalLogOfDPM NUMERIC(12,4)
    DECLARE @NaturalLogOfDPMTimesZ NUMERIC(12,4)
    DECLARE @CalculatedValueForSQR NUMERIC(12,4)
    DECLARE @SQROfCalculatedValue NUMERIC(12,4)

    DECLARE @Result NUMERIC(12,4)


    SET @X = 0.8406
    SET @Y = 29.37
    SET @Z = 2.221

    IF @DefectQty <=0 OR @OrderQty < @DefectQty
        SET @Result = -99
    ELSE

        BEGIN

            SET @DPM=  1000000 / @OrderQty * @DefectQty

            SET @NaturalLogOfDPM= log(@DPM)
            SET @NaturalLogOfDPMTimesZ = @NaturalLogOfDPM * @Z
            SET @CalculatedValueForSQR = @Y - @NaturalLogOfDPMTimesZ
            SET @SQROfCalculatedValue = SQRT(@CalculatedValueForSQR)


            SET @Result = @X + @SQROfCalculatedValue

        End

    -- Return the result of the function
    RETURN @Result

END
GO
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com