Comparing Values To Specification Limits

Comparing variable values to specifications limits involves a multi-level JOIN involving the variable, product and the time stamp of the value. Variable values in the Tests table and limits in the Var_Specs table are both stored in varchar(25) format so a proper explicit conversion of all fields should be made to ensure the correct comparison. Otherwise, SQL may implicitly make the wrong conversion (for example, varchar to integer instead of varchar to real) and cause unexpected behavior.

Consideration of the SpecificationSetting site parameter (Parm_Id = 13) should also be taken. This site parameter controls whether a value equal to a limit is out-of-spec or not. It primarily affects the way specification deviations are displayed in the Plant Applications Clients. SpecificationSetting has two possible values and they are as follows:

Value	Description
1	The value is considered out-of-spec if Lower Limit > Value > Upper Limit
2	The value is considered out-of-spec if Lower Limit >= Value >= Upper Limit

The following is an example query against specifications limits


DECLARE @SpecificationSetting	int

SELECT @SpecificationSetting = Value 
FROM Site_Parameters
WHERE Parm_Id = 13

SELECT	v.Var_Desc, 
        t.Result, 
        vs.L_Reject, 
        vs.L_Warning, 
        vs.L_User, 
        vs.Target, 
        vs.U_User, 
        vs.U_Warning, 
        vs.U_Reject,
CASE @SpecificationSetting
       WHEN 1 THEN
              CASE	WHEN convert(float, t.Result) > convert(float, vs.U_Warning) 
                               THEN ' WARNING'
                               ELSE '' 
                               END
      WHEN 2 THEN
             CASE	WHEN convert(float, t.Result) >= convert(float, vs.U_Warning) 
                                THEN 'WARNING'
                                ELSE '' 
                                END
                       END 
FROM Tests t 
     JOIN Variables v ON t.Var_Id = v.Var_Id
              JOIN Production_Starts ps ON	v.PU_Id = ps.PU_Id
                                          AND t.Result_On >= ps.Start_Time 
                                          AND (	t.Result_On < ps.End_Time
                                                OR ps.End_Time IS NULL) 
                   LEFT JOIN Var_Specs vs ON t.Var_Id = vs.Var_Id
                                               AND ps.Prod_Id = vs.Prod_Id
                                               AND t.Result_On >= vs.Effective_Date 
                                               AND (	t.Result_On < vs.Expiration_Date
                                                        OR vs.Expiration_Date IS NULL)
WHERE		t.Var_Id = @ReportVarId 
                AND t.Result_On > @ReportStartTime 
                AND t.Result_On < @ReportEndTime