Help with a nested query in MySQL

Setup test case:

CREATE TABLE `m1`
(c1 DATE
,c2 VARCHAR(6)
,c3 SMALLINT
,c4 TINYINT
) DEFAULT CHARSET=latin1;

INSERT INTO `m1` VALUES
 ('2011-02-19','Test-A',31,3)
,('2011-02-19','Test-B',34,3)
,('2011-02-19','Test-C',17,1)
,('2011-02-15','Test-A',48,4)
,('2011-02-15','Test-B',64,6)
,('2011-02-15','Test-C',55,5)
,('2011-02-11','Test-A',64,6)
,('2011-02-11','Test-B',53,5)
,('2011-02-11','Test-C',17,1)
,('2011-02-10','Test-A',12,1)
,('2011-02-10','Test-B',02,0)
,('2011-02-10','Test-C',54,5);

This query makes use of one local variable (@i). Provide the test_name ('Test-A') and the date ('2011-02-17') in the query, shown as literals here.

SELECT o.tn AS `Test`
     , o.dt AS `Date`
     , SUM(CASE WHEN o.n = 1 THEN o.c3*1.00 ELSE NULL END) AS R
     , SUM(CASE WHEN o.n = 1 THEN o.c3*0.23 WHEN o.n = 2 THEN -1.00*o.c3 ELSE NULL END) AS R2
     , SUM(CASE WHEN o.n = 1 THEN o.c3*0.23 WHEN o.n = 3 THEN -1.00*o.c3 ELSE NULL END) AS R3
     , AVG(CASE WHEN o.n < 4 THEN c3*1.00 ELSE NULL END)-SUM(CASE WHEN n = 3 THEN c4*1.00 ELSE NULL END) AS R4
  FROM (
         SELECT @i := @i + 1 AS n
              , s.tn
              , s.dt
           -- , m.c1
              , m.c3
              , m.c4
           FROM (SELECT '2011-02-17' AS dt,_latin1'Test-A' AS tn, @i := 0) s
           JOIN m1 m
             ON m.c2 = s.tn AND m.c1 <= s.dt
          ORDER BY m.c1 DESC
          LIMIT 0,3
       ) o
 GROUP BY o.tn, o.dt
 HAVING SUM(1) >= 3

You can run just the inner query, uncomment the m.c1 from the select list, to check the rows returned (1st, 2nd and 3rd latest, prior to the supplied date.

This query returns a different value for R3 than shown in the question, but the result returned by the query appears to be the correct result for the given formula.

Also, the formula for R4 references 5 values: avg(I,I1,I2,I3)-J3. The formula used in the query is effectively =avg(I1,I2,I3)-J3

To get the result for all tests, as of a given date:

SELECT o.tn AS `Test`
     , o.dt AS `Date`
     , SUM(CASE WHEN o.n = 1 THEN o.c3 ELSE NULL END) AS R
     , SUM(CASE WHEN o.n = 1 THEN o.c3*0.23 WHEN o.n = 2 THEN -1.00*o.c3 ELSE NULL END) AS R2
     , SUM(CASE WHEN o.n = 1 THEN o.c3*0.23 WHEN o.n = 3 THEN -1.00*o.c3 ELSE NULL END) AS R3
     , AVG(CASE WHEN o.n <= 3 THEN c3*1.00 ELSE NULL END)-SUM(CASE WHEN n = 3 THEN c4 ELSE NULL END) AS R4
  FROM (
         SELECT @i := CASE WHEN @prev_tn = m.c2 THEN @i + 1 ELSE 1 END AS n
              , @prev_dt := s.dt AS dt
              , @prev_tn := m.c2 AS tn
              , m.c1
              , m.c3
              , m.c4
           FROM (SELECT '2011-02-17' AS dt, @i := 0, @prev_tn := NULL) s
           JOIN m1 m
             ON m.c1 <= s.dt
          ORDER BY s.dt, m.c2, m.c1 DESC
       ) o
GROUP BY o.tn, o.dt
HAVING SUM(1) >= 3 

(The HAVING clause guarantees that the query returns results only if there are at least three rows for a given test, preceding the given date.) Here is the query output for two different dates, the 17th and the 20th:

Test    Date        R   R2      R3      R4
------  ----------  --  ------  ------  -----
Test-A  2011-02-17  48  -52.96  -0.96   40.33
Test-B  2011-02-17  64  -38.28  12.72   39.67
Test-C  2011-02-17  55  -4.35   -41.35  37.00

Test    Date        R   R2      R3      R4
------  ----------  --  ------  ------  -----
Test-A  2011-02-20  31  -40.87  -56.87  41.67
Test-B  2011-02-20  34  -56.18  -45.18  45.33
Test-C  2011-02-20  17  -51.09  -13.09  28.67

(The query would be somewhat more involved, to get results for more than one date.)

This may not be the best way to solve the problem, but I've successfully used this approach with MySQL.