/*
This is an
example of Dunnet's test.
There are a
lot of relational database ideas at work here. Some
of them are, how to work with data in columns. How to work
with sets
and to maintain sequential order when needed. How to iterate
with a
cursor or a SELECT statement. And, how to solve a problem
that has
experimental relevance.
Run the script
statement by statement after loading the initial
tables. Change things around to see how things work.
There are a
couple of typos to be aware of in the reference. It looks
like the the order of the sample means(<) is reversed and
the answers are given
all as <Uc - Ua<. Minor details for a great example in
analysis. Also a
challenging example to write some database code to.
By C. Eric
Cashon
Reference:
DeMuth J. E.,Basic Statistics and Pharmaceutical Statistical
Applications,
Chapman & Hall/CRC, FL, 2006, P.239-242.
*/
USE tempdb
CREATE TABLE Dunnets(DunID INTEGER NOT NULL IDENTITY PRIMARY
KEY,
GroupID1 INTEGER, GroupID2 INTEGER, TestValue REAL)
CREATE TABLE Lookup(LookupID INTEGER, Value Real)
INSERT INTO Lookup(LookupID, Value) VALUES(1, 2.00)
INSERT INTO Lookup(LookupID, Value) VALUES(2, 2.00)
INSERT INTO Lookup(LookupID, Value) VALUES(3, 2.27)
INSERT INTO Lookup(LookupID, Value) VALUES(4, 2.41)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 52)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 60)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 45)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 47)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 43)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 57)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 40)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 47)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 43)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
1, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 57)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 54)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
2, 59)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 60)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 54)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 69)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 66)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
3, 64)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 45)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 52)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(1,
4, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 52)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 60)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 45)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 47)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 43)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 57)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 40)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 47)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 43)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
1, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 60)--57 to 60
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 54)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 49)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
2, 59)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 63)--60 to 63
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 56)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 54)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 50)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 69)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 53)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 66)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
3, 64)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 56)--53 to 56
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 45)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 46)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 58)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 61)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 52)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 51)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 55)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 48)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 62)
INSERT INTO Dunnets(GroupID1, GroupID2, TestValue) VALUES(2,
4, 49)
SELECT * FROM Dunnets
--Calculate some values that will be needed. Order the means
--when a new table is built later.
CREATE VIEW OrderMeans AS
SELECT GroupID1,
GroupID2,
COUNT(GroupID2) AS
Deg, AVG(TestValue) AS Mean,
STDEV(TestValue) AS
SD
FROM Dunnets
GROUP BY GroupID2,
GroupID1
SELECT * FROM OrderMeans
--Find MSw(mean squared within) MSw = 28.93 and 29.87
CREATE VIEW MSw1 AS
SELECT GroupID1,
SUM((Deg - 1) * (SQUARE(SD)))/ (SUM(Deg) - 4) AS MSw
FROM OrderMeans
GROUP BY GroupID1
SELECT * FROM MSw1
--Put MSw and OrderedMeans Views together.
CREATE VIEW OrderMeans2 AS
SELECT T1.GroupID1,
T1.GroupID2, T1.Deg, T1.Mean, T1.SD, T2.MSw
FROM OrderMeans T1
INNER JOIN MSw1 T2
ON T1.GroupID1 =
T2.GroupID1
SELECT * FROM OrderMeans2 ORDER BY GroupID1, Mean
--Create a new table with the values and order the means.
SELECT * INTO Dunnets2 FROM OrderMeans2 ORDER BY GroupID1,
Mean
SELECT * FROM OrderMeans2
--add two new fields. A RankID to join to the Lookup table
--and a DunnID2 to iterate down the table.
ALTER TABLE Dunnets2 ADD RankID INTEGER, DunnID2 INTEGER
--Rank the means and order the table. This will work with
the example
--or if the control is the top or bottom of a set of four.
If the control is
--in the middle of a set like X1 < Xc < X2 < X3 you
will have to write
--some different code to set values to join the Lookup
table.
DECLARE CurRank CURSOR FOR
SELECT RankID FROM
Dunnets2
OPEN CurRank
DECLARE @i INTEGER
DECLARE @j INTEGER
SET @i = 1
SET @j = 1
FETCH NEXT FROM
CurRank
WHILE
@@FETCH_STATUS = 0
BEGIN
UPDATE Dunnets2
SET RankID = @i, DunnID2 = @j
WHERE CURRENT
OF CurRank
IF @i < 4
BEGIN
SET @i = @i
+ 1
END
ELSE
BEGIN
SET @i = 1
END
SET @j = @j + 1
FETCH NEXT FROM
CurRank
END
CLOSE CurRank
DEALLOCATE CurRank
SELECT * FROM Dunnets2
--The values that are needed for the calculation.
CREATE VIEW OrderMeans3 AS
SELECT T1.GroupID1,
T1.GroupID2, T1.Mean, T1.RankID,
T1.DunnID2, T1.MSw, T2.LookupID, T2.Value
FROM Dunnets2 T1
INNER JOIN Lookup T2
ON T1.RankID =
T2.LookupID
SELECT * FROM OrderMeans3
--Create a new table with all of the needed values.
SELECT * INTO Dunnets3 FROM OrderMeans3
SELECT * FROM Dunnets3
--Put it all together in a query and get the answer.
SELECT T2.DunnID2, T2.GroupID1, T2.GroupID2, T2.RankID,
T2.MSw, T2.DValue,
((T2.ControlMean - T2.Test) - (T2.DValue * SQRT(T2.MSw * (1.0/24.0 +
1.0/12.0)))) AS LowB,
((T2.ControlMean - T2.Test) + (T2.DValue * SQRT(T2.MSw * (1.0/24.0 +
1.0/12.0)))) AS HighB
FROM
(SELECT
T1.DunnID2, T1.MSw, T1.GroupID1, T1.GroupID2, T1.RankID,
(SELECT 'ControlMean' = CASE
WHEN T1.DunnID2 % 4 <>
0 THEN
(SELECT Mean FROM Dunnets3 T3
WHERE T3.DunnID2 = (T1.DunnID2
/ 4) * 4 + 1)
ELSE
(SELECT Mean FROM Dunnets3 T4
WHERE T4.DunnID2 = ((T1.DunnID2
- 1) / 4) * 4 + 1)
END) AS ControlMean,
(SELECT 'Test' = CASE
WHEN T1.DunnID2 % 1
<> 0 THEN
(SELECT Mean FROM Dunnets3 T7
WHERE T7.DunnID2 =
(T1.DunnID2 / 1) * 1 + 1 )
ELSE
(SELECT Mean
FROM Dunnets3 T8
WHERE T8.DunnID2 =
((T1.DunnID2 - 1) / 1) * 1 + 1 )
END) AS Test,
(SELECT 'DValue' = CASE
WHEN T1.DunnID2 % 1 <> 0
THEN
(SELECT Value FROM Dunnets3 T7
WHERE T7.DunnID2 =
(T1.DunnID2 / 1) * 1 + 1 )
ELSE
(SELECT Value FROM Dunnets3
T8
WHERE T8.DunnID2 =
((T1.DunnID2 - 1) / 1) * 1 + 1 )
END) AS DValue
FROM Dunnets3 T1)
AS T2
--Clean up
DROP TABLE Dunnets
DROP TABLE Dunnets2
DROP TABLE Dunnets3
DROP TABLE Lookup
DROP VIEW MSw1
DROP VIEW OrderMeans
DROP VIEW OrderMeans2
DROP VIEW OrderMeans3