LISTING 2: Using a Temporary Table and a Loop to Calculate Product

SELECT testid, caseid, result, 0 AS used
INTO #T1
FROM Testresults

WHILE @@rowcount > 0
  UPDATE #T1
    SET result = result *
          ISNULL((SELECT TOP 1 result
                  FROM #T1 AS T3
                  WHERE T3.caseid = T2.caseid
                    AND T3.testid < T2.testid
                    AND used = 1
                  ORDER BY testid DESC), 1),
        used = 1
    FROM #T1 AS T2
    WHERE testid = (SELECT MIN(testid)
                    FROM #T1
                    WHERE used = 0)

SELECT caseid, MAX(result) AS 'final result'
FROM #T1
GROUP BY caseid

DROP TABLE #T1