SELECT 
    test_id,
    screening_method_1,
    screening_method_2,
    screening_method_3,
    confirmatory_result,
    CASE 
        -- HIV Positive
        WHEN screening_method_1 = 'Positive' 
             AND screening_method_2 = 'Positive' 
             AND screening_method_3 = 'Positive' 
             AND confirmatory_result = 'Positive' 
        THEN 'HIV Positive'

        -- HIV Negative
        WHEN screening_method_1 = 'Negative' 
             AND screening_method_2 = 'Negative' 
             AND screening_method_3 = 'Negative' 
             AND confirmatory_result = 'Negative' 
        THEN 'HIV Negative'

        -- Indeterminate (Further Testing Required)
        WHEN screening_method_1 = 'Indeterminate' 
             OR screening_method_2 = 'Indeterminate' 
             OR screening_method_3 = 'Indeterminate' 
             OR confirmatory_result = 'Indeterminate' 
        THEN 'Indeterminate (Further Testing Required)'

        -- Indeterminate (Inconsistent Screening Results)
        WHEN (screening_method_1 != screening_method_2) 
             OR (screening_method_2 != screening_method_3) 
             OR (screening_method_1 != screening_method_3) 
        THEN 'Indeterminate (Further Testing Required)'

        -- Invalid Test Result
        ELSE 'Invalid Test Result'
    END AS final_result
FROM 
    hiv_tests;