/*

This is an example of Dunn's Multiple Comparisons Test. A good

example of generating combinations in SQL and using the output to generate

values in a statistical multiple comparison test.

By C. Eric Cashon

Reference:

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

Chapman & Hall/CRC, FL, 2006, P.237-239.

*/

USE tempdb

CREATE TABLE Dunn(DunnID INTEGER, Formulation CHAR(1), Mean REAL,

n REAL, MSe REAL, Dunns REAL)

INSERT INTO Dunn(DunnID, Formulation, Mean, n, MSe, Dunns)

VALUES(1, 'A', 123.2, 20, 153.51, 2.72)

INSERT INTO Dunn(DunnID, Formulation, Mean, n, MSe, Dunns)

VALUES(2, 'B', 105.6, 20, 153.51, 2.72)

INSERT INTO Dunn(DunnID, Formulation, Mean, n, MSe, Dunns)

VALUES(3, 'C', 116.4, 19, 153.51, 2.72)

INSERT INTO Dunn(DunnID, Formulation, Mean, n, MSe, Dunns)

VALUES(4, 'D', 113.5, 18, 153.51, 2.72)

SELECT * FROM Dunn

--Get all the possible combinations of 2.

CREATE VIEW PossibleCombos(Formulation1, Formulation2, Mean1, Mean2, n1, n2, MSe, Dunns) AS

SELECT T1.Formulation, T2.Formulation, T1.Mean, T2.Mean, T1.n, T2.n,

T1.MSe, T1.Dunns

FROM Dunn T1 INNER JOIN Dunn T2

ON T1.DunnID < T2.DunnID

SELECT * FROM PossibleCombos ORDER BY Formulation1

--Get the answer

SELECT Formulation1, Formulation2, Mean1, Mean2, n1, n2, MSe, Dunns,

ABS(Mean1 - Mean2) AS MeanDiff,

(Mean1 - Mean2) - Dunns * (SQRT(MSe * ((1 / n1) + (1 / n2)))) AS Lb,

'< U' + Formulation1 + ' - U' + Formulation2 + ' <' AS Pairing,

(Mean1 - Mean2) + Dunns * (SQRT(MSe * ((1 / n1) + (1 / n2)))) AS Ub

FROM PossibleCombos ORDER BY Formulation1

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

--Start of second Example.

/*

Don't be limited to one set of comparisons. Take advantage of the relational

model and plan your experiment with as many sets as you want.

*/

CREATE TABLE Dunn2(DunnID1 INTEGER, DunnID2 INTEGER, Formulation CHAR(1),

Mean REAL, n REAL, MSe REAL, Dunns REAL)

--Changed the MSe values in the second and third set. Change different

--values to see the changes in the results.

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(1, 1, 'A', 123.2, 20, 153.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(2, 1, 'B', 105.6, 20, 153.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(3, 1, 'C', 116.4, 19, 153.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(4, 1, 'D', 113.5, 18, 153.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)--2nd set

VALUES(5, 2, 'A', 123.2, 20, 152.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(6, 2, 'B', 105.6, 20, 152.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(7, 2, 'C', 116.4, 19, 152.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(8, 2, 'D', 113.5, 18, 152.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)--3rd set

VALUES(9, 3, 'A', 123.2, 20, 151.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(10, 3, 'B', 105.6, 20, 151.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(11, 3, 'C', 116.4, 19, 151.51, 2.72)

INSERT INTO Dunn2(DunnID1, DunnID2, Formulation, Mean, n, MSe, Dunns)

VALUES(12, 3, 'D', 113.5, 18, 151.51, 2.72)

SELECT * FROM Dunn2

--Create a new table for the combinations

CREATE TABLE PossibleCombos2(GroupID INTEGER, Formulation1 CHAR(1), Formulation2 CHAR(2), Mean1 REAL,

Mean2 REAL, n1 REAL, n2 REAL, MSe Real, Dunns REAL)

--Get combinations within groups and load them into the

--PossibleCombos table.

DECLARE @i INTEGER

SET @i = 1

WHILE @i <= 3

BEGIN

INSERT INTO PossibleCombos2(GroupID, Formulation1, Formulation2, Mean1, Mean2, n1, n2, MSe, Dunns)

(SELECT T1.DunnID2, T1.Formulation, T2.Formulation, T1.Mean, T2.Mean, T1.n, T2.n,

T1.MSe, T1.Dunns

FROM Dunn2 T1 INNER JOIN Dunn2 T2

ON T1.DunnID1 < T2.DunnID1

WHERE T1.DunnID2 = @i AND T2.DunnID2 = @i)

SET @i = @i + 1

END

--Take a look at the results.

SELECT * FROM PossibleCombos2 ORDER BY GroupID, Formulation1

--Get the answer

SELECT Formulation1, Formulation2, Mean1, Mean2, n1, n2, MSe, Dunns,

ABS(Mean1 - Mean2) AS MeanDiff,

(Mean1 - Mean2) - Dunns * (SQRT(MSe * ((1 / n1) + (1 / n2)))) AS Lb,

'< U' + Formulation1 + ' - U' + Formulation2 + ' <' AS Pairing,

(Mean1 - Mean2) + Dunns * (SQRT(MSe * ((1 / n1) + (1 / n2)))) AS Ub

FROM PossibleCombos2 ORDER BY GroupID, Formulation1

--Clean up.

DROP TABLE Dunn2

DROP TABLE PossibleCombos2