Creating T-SQL Summary Rows

Question

I have data in a table that looks like the following sample data:

 varchar(20)    | DateTime               | varchar(20) |varchar(255)
_Serial_Number__|_Date_Time______________|_System_ID___|_Test_Result________________
C035993 0703 05 |2005-08-18 13:43:33.717 |VTI-Chamber1 | (BLUE) TEST ABORTED, LEAKFP SPUN DOWN
C035993 0702 05 |2005-08-18 13:51:52.640 |VTI-Chamber1 | FAIL: Squirt Test.
C035993 0704 05 |2005-08-18 14:18:13.607 |VTI-Chamber1 | TEST ABORTED
C035993 0705 05 |2005-08-18 14:30:43.717 |VTI-Chamber1 | B=FAIL, Final N2 Fill after Settle, W=PASS,
C035993 0707 05 |2005-08-18 14:41:59.310 |VTI-Chamber1 | FAIL: Fine Test.
C035878 0775 05 |2005-08-18 15:38:25.810 |VTI-Chamber1 | Chamber Calibration Factor Too High
C035878 0774 05 |2005-08-18 15:43:23.000 |VTI-Chamber1 | FAIL Pressure Decay Test
C035993 0674 05 |2005-08-18 15:51:49.467 |VTI-Chamber1 | FAIL: Squirt Test.
BLANKTEST       |2005-08-18 15:58:40.793 |VTI-Chamber3 | Pass.
C035993 0706 05 |2005-08-18 15:59:03.200 |VTI-Chamber1 | Pass.

I need to create a couple of scripts go through all of the records for a given Serial_Number and determine if it passed or failed. There are generally multiple entries for each part.

One test needs to determine the status of the part, or whether the last test result was a PASS or a FAIL, ignoring data such as 'TEST ABORTED' or 'Chamber Calibration Factor Too High'.

The second test needs to determine the quality of the part, and the criteria we use for that is to check to see if the part passed on the first test, again ignoring erroneous data such as 'TEST ABORTED' or 'Chamber Calibration Factor Too High'.

I feel like I need to create something that selects the distinct serial number, then write a while loop that iterates over the data.

I've got something working, but I do not currently have a way to get my returned data to be sorted by the Date_Time field.

If I can get that part figured out, I should be set.

Could someone kindly show me what I could do to allow my script to filter by the Date_Time field?

declare @result varChar(10), @serialNum varChar(20), @testResult varChar(255)
declare snList cursor for
    select distinct TR.Serial_Number
    from Test_Results TR
    left join ACP_Parts AP on (TR.Serial_Number=AP.Serial_Number)
    where (AP.Serial_Number is not null)
open snList
fetch next from snList into @serialNum
while (@@fetch_status=0) begin
    set @result=''
    declare resultList Cursor for
        select Test_Result
        from Test_Results
        where (Serial_Number=@serialNum) and (System_ID Like '%Chamb%')
    open resultList
    fetch next from resultList into @testResult
    while (@@fetch_status=0) and (@result<>'PASS') begin
        set @result=case
            when (0<CharIndex('fail', @testResult)) then 'FAIL'
            when (0<CharIndex('pass', @testResult)) then 'PASS'
            else ''
        end
    end
    close resultList
    select @serialNum as 'Serial_Number', @result as 'Test_Result'
    fetch next from snList into @serialNum
end
close snList

End Of File.

Answer 1

Here's my best guess for what you're trying to do. Other than using a query similar to this, my next suggestion is to hire someone who knows SQL and has programming experience or get up to speed on it yourself.

SELECT
    TR1.serial_number,
    CASE
        WHEN TR1.test_result LIKE '%pass%' THEN 'Pass'
        WHEN TR1.test_result LIKE '%fail%' THEN 'Fail'
        ELSE NULL
    END AS final_result
FROM
    Test_Results TR1
LEFT OUTER JOIN Test_Results TR2 ON
    TR2.serial_number = TR1.serial_number AND
    (
        TR2.test_result LIKE '%pass%' OR
        TR2.test_result LIKE '%fail%'
    ) AND
    TR2.test_date > TR1.test_date
WHERE
    (
        TR1.test_result LIKE '%pass%' OR
        TR1.test_result LIKE '%fail%'
    ) AND
    TR2.serial_number IS NULL

No cursors and a single statement. The LEFT OUTER JOIN is basically looking to see if there are any later rows with a pass or fail for the same serial number. If there aren't, TR2.serial_number will be NULL and the TR1 row will therefor be the latest test result with a pass or fail.

If you can further constraint the pass/fail criteria then that would be a good idea so that you don't accidentally use erroneous results (for example, LIKE 'pass%' would be better than LIKE '%pass%').

This solution may have a problem if two pass/fail results come through with the same exact date_time value. That will likely be a problem with any solution though unless you decide how to handle that.