/*

This is an example of a two sample t-test. The first example follows

the reference. The second example shows why you might want all your

values in one column if you are doing more than one test. The third

example applies the example to 96 well plates in an initial

screen in HTS were each set is a different compound at one

concentration compared to a control. In HTS you don't get this

many replicate wells so you lose out on a lot of the power of

the Central Limit Theorem. All things to consider in experimental

design.

The following reference is a clear explanation of the mathematics

and the interpretation of the numbers. The code examples show how

you can calculate the numbers with some programming ideas involved.

By C. Eric Cashon

Reference:

DeMuth J. E.,Basic Statistics and Pharmaceutical Statistical Applications,

Marcel Dekker, NY, 1999, P.180

*/

USE Tempdb

CREATE TABLE tTest(TestID INTEGER NOT NULL IDENTITY,

TestSample1 REAL, TestSample2 Real)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(125, 130)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(130, 128)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(135, 127)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(126, 149)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(140, 151)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(135, 130)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(128, 141)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(121, 145)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(123, 132)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(126, 132)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(121, 141)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(133, 129)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(131, 133)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(129, 136)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(120, 138)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(117, 142)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(126, 130)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(127, 122)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(119, 129)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(133, 150)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(125, 148)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(120, 136)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(136, 138)

INSERT INTO tTest(TestSample1, TestSample2) VALUES(122, 140)

SELECT * FROM tTest

SELECT AVG(TestSample1) Mean1, STDEV(TestSample1) SD1, AVG(TestSample2) Mean2,

STDEV(TestSample2) SD2 FROM tTest

--Find S2

SELECT (((23 * SQUARE(STDEV(TestSample1))) + (23 * SQUARE(STDEV(TestSample2))))

/ (24 + 24 - 2)) Ssquared

FROM tTest

--Get the answer

SELECT (AVG(TestSample1) - AVG(TestSample2)) - (2.01 * SQRT(2 *

(((23 * SQUARE(STDEV(TestSample1))) + (23 * SQUARE(STDEV(TestSample2))))

/ (24 + 24 - 2)) / 24)),

' < Ua - Ub < ' AS Diff,

(AVG(TestSample1) - AVG(TestSample2)) + (2.01 * SQRT(2 *

(((23 * SQUARE(STDEV(TestSample1))) + (23 * SQUARE(STDEV(TestSample2))))

/ (24 + 24 - 2)) / 24))

FROM tTest

--Clean up

DROP TABLE tTest

--------------------------------------------------------------------------------

--Start of second example.

CREATE TABLE tTest2(TestID INTEGER NOT NULL IDENTITY PRIMARY KEY,

GroupID INTEGER, TestSample1 REAL)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 140)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 123)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 131)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 117)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 119)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 149)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 151)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 145)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 142)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 150)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 148)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(1, 140)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 140)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 123)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 131)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 117)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 119)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 149)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 151)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 145)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 142)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 150)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 148)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(2, 135) --Change from 140 to 135

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 140)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 123)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 131)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 117)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 119)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 149)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 151)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 145)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 142)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 150)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 148)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(3, 130) --Change from 140 to 130

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 140)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 135)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 123)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 121)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 131)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 117)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 126)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 119)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 125)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 120)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 128)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 127)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 149)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 151)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 145)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 132)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 141)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 133)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 142)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 130)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 122)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 129)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 150)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 148)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 136)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 138)

INSERT INTO tTest2(GroupID, TestSample1) VALUES(4, 125) --Change from 140 to 125

SELECT * FROM tTest2

--Compare 2 sets of 24 down a column. Four different comparisons

--are made in this example.

SELECT DISTINCT T2.GroupID, (T2.AVG1 - T2.AVG2) - (2.01 * SQRT(2 *

(((23 * SQUARE(T2.SD1)) + (23 * SQUARE(T2.SD2)))

/ (24 + 24 - 2)) / 24)) AS FirstSet,

' < Ua - Ub < ' AS Diff,

(T2.AVG1 - T2.AVG2) + (2.01 * SQRT(2 *

(((23 * SQUARE(T2.SD1)) + (23 * SQUARE(T2.SD2)))

/ (24 + 24 - 2)) / 24)) AS SecondSet

FROM

(SELECT T1.TestID, T1.GroupID, (SELECT 'AVG1' = CASE

WHEN T1.TestID % 48 <> 0 THEN

(SELECT (AVG(T3.TestSample1)) FROM tTest2 T3

WHERE T3.TestID >= (T1.TestID / 48) * 48 + 1

AND T3.TestID <= (T1.TestID / 48) * 48 + 24

)

ELSE

(SELECT (AVG(T4.TestSample1)) FROM tTest2 T4

WHERE T4.TestID >= ((T1.TestID - 1) / 48) * 48 + 1

AND T4.TestID <= ((T1.TestID - 1) / 48) * 48 + 24

)

END) AS AVG1,

(SELECT 'SD1' = CASE

WHEN T1.TestID % 48 <> 0 THEN

(SELECT (STDEV(T5.TestSample1)) FROM tTest2 T5

WHERE T5.TestID >= (T1.TestID / 48) * 48 + 1

AND T5.TestID <= (T1.TestID / 48) * 48 + 24

)

ELSE

(SELECT (STDEV(T6.TestSample1)) FROM tTest2 T6

WHERE T6.TestID >= ((T1.TestID - 1) / 48) * 48 + 1

AND T6.TestID <= ((T1.TestID - 1) / 48) * 48 + 24

)

END) AS SD1,

(SELECT 'AVG2' = CASE

WHEN T1.TestID % 48 <> 0 THEN

(SELECT (AVG(T7.TestSample1)) FROM tTest2 T7

WHERE T7.TestID >= (T1.TestID / 48) * 48 + 25

AND T7.TestID <= (T1.TestID / 48) * 48 + 48

)

ELSE

(SELECT (AVG(T8.TestSample1)) FROM tTest2 T8

WHERE T8.TestID >= ((T1.TestID - 1) / 48) * 48 + 25

AND T8.TestID <= ((T1.TestID - 1) / 48) * 48 + 48

)

END) AS AVG2,

(SELECT 'SD2' = CASE

WHEN T1.TestID % 48 <> 0 THEN

(SELECT (STDEV(T9.TestSample1)) FROM tTest2 T9

WHERE T9.TestID >= (T1.TestID / 48) * 48 + 25

AND T9.TestID <= (T1.TestID / 48) * 48 + 48

)

ELSE

(SELECT (STDEV(T10.TestSample1)) FROM tTest2 T10

WHERE T10.TestID >= ((T1.TestID - 1) / 48) * 48 + 25

AND T10.TestID <= ((T1.TestID - 1) / 48) * 48 + 48

)

END) AS SD2

FROM tTest2 T1) AS T2

--Clean up

DROP TABLE tTest2

--Third example

--Compare independent control and test sets on multiple 96-well

--plates. The first set of 24 is the control and then the next

--three sets of 24 are the test sets. Four comparisons in each

--96-well plate. The control with the control and then the control

--with each of the 3 test sets.

CREATE TABLE tTest3(TestID INTEGER NOT NULL IDENTITY PRIMARY KEY,

GroupID INTEGER, TestSample1 REAL)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 125)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 135)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 140)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 135)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 121)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 123)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 121)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 131)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 120)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 117)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 119)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 125)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 120)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 122)--Control

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 140)--Test1

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 135)--Test2 Change 140 to 135

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(1, 130) --Test3 Change from 140 to 130

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 125)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 135)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 140)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 135)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 121)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 123)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 121)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 131)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 120)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 117)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 126)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 119)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 125)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 120)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 122)--Control

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 125) --Test1 Change from 140 to 125

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 120) --Test2 Change from 140 to 120

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 128)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 127)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 149)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 151)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 145)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 132)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 141)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 133)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 142)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 130)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 122)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 129)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 150)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 148)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 136)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 138)

INSERT INTO tTest3(GroupID, TestSample1) VALUES(2, 115) --Change from 140 to 115

SELECT * FROM tTest3

--Change the set iterators in the above example to accomplish this.

--Notice when the comparisons are returned they may not be in order.

--Remove the DISTINCT keyword and re-run the query. Even though the

--interators may work in order the DISTINCT keyword may not maintain

--it.

SELECT DISTINCT T2.GroupID, (T2.AVG1 - T2.AVG2) - (2.01 * SQRT(2 *

(((23 * SQUARE(T2.SD1)) + (23 * SQUARE(T2.SD2)))

/ (24 + 24 - 2)) / 24)) AS FirstSet,

' < Ua - Ub < ' AS Diff,

(T2.AVG1 - T2.AVG2) + (2.01 * SQRT(2 *

(((23 * SQUARE(T2.SD1)) + (23 * SQUARE(T2.SD2)))

/ (24 + 24 - 2)) / 24)) AS SecondSet

FROM

(SELECT T1.TestID, T1.GroupID, (SELECT 'AVG1' = CASE

WHEN T1.TestID % 96 <> 0 THEN

(SELECT (AVG(T3.TestSample1)) FROM tTest3 T3

WHERE T3.TestID >= (T1.TestID / 96) * 96 + 1

AND T3.TestID <= (T1.TestID / 96) * 96 + 24

)

ELSE

(SELECT (AVG(T4.TestSample1)) FROM tTest3 T4

WHERE T4.TestID >= ((T1.TestID - 1) / 96) * 96 + 1

AND T4.TestID <= ((T1.TestID - 1) / 96) * 96 + 24

)

END) AS AVG1,

(SELECT 'SD1' = CASE

WHEN T1.TestID % 96 <> 0 THEN

(SELECT (STDEV(T5.TestSample1)) FROM tTest3 T5

WHERE T5.TestID >= (T1.TestID / 96) * 96 + 1

AND T5.TestID <= (T1.TestID / 96) * 96 + 24

)

ELSE

(SELECT (STDEV(T6.TestSample1)) FROM tTest3 T6

WHERE T6.TestID >= ((T1.TestID - 1) / 96) * 96 + 1

AND T6.TestID <= ((T1.TestID - 1) / 96) * 96 + 24

)

END) AS SD1,

(SELECT 'AVG2' = CASE

WHEN T1.TestID % 24 <> 0 THEN

(SELECT (AVG(T7.TestSample1)) FROM tTest3 T7

WHERE T7.TestID >= (T1.TestID / 24) * 24 + 1

AND T7.TestID <= (T1.TestID / 24) * 24 + 24

)

ELSE

(SELECT (AVG(T8.TestSample1)) FROM tTest3 T8

WHERE T8.TestID >= ((T1.TestID - 1) / 24) * 24 + 1

AND T8.TestID <= ((T1.TestID - 1) / 24) * 24 + 24

)

END) AS AVG2,

(SELECT 'SD2' = CASE

WHEN T1.TestID % 24 <> 0 THEN

(SELECT (STDEV(T9.TestSample1)) FROM tTest3 T9

WHERE T9.TestID >= (T1.TestID / 24) * 24 + 1

AND T9.TestID <= (T1.TestID / 24) * 24 + 24

)

ELSE

(SELECT (STDEV(T10.TestSample1)) FROM tTest3 T10

WHERE T10.TestID >= ((T1.TestID - 1) / 24) * 24 + 1

AND T10.TestID <= ((T1.TestID - 1) / 24) * 24 + 24

)

END) AS SD2

FROM tTest3 T1) AS T2

--Clean up

DROP TABLE tTest3