--This is an applied example of permutations in the design of a

--Latin-Square Crossover Bioequivalency study. It is good to know

--how a relational cross-join(Cartesian Product) can be used to

--generate tables of combinations and permutations. Very helpful in

--experimental design.

--By C. Eric Cashon

--Reference1: Shargel L., Wu-Pong S., Yu A. B. C.,;

-- Applied Biopharmaceutics & Pharmacokinetics 5th ed.

-- McGraw-Hill, 2005, P.472.

--Reference2:DeMuth J. E.,Basic Statistics and Pharmaceutical Statistical Applications,

-- Chapman & Hall/CRC, FL, 2006, P.291-297.

CREATE TABLE DrugProduct( DrugID INTEGER,

Drug CHAR(1));

INSERT INTO DrugProduct VALUES( 1, 'A')

,( 2, 'B')

,( 3, 'C')

,( 4, 'D');

--Permutations. Compare with the combination example(DB2_UniquePairs4.sql) where

--the order is not important.

--P = n!/(n - k)!

--P = 4!/(4 - 4)!

--P = 24

--Notice that 24 records are generated. In the first reference above, a sub-set of 16 records

--is used. In the following query, I get record numbers 2,4,9,12,15,18,19,20 as being

--the other 8 possible combinations.

SELECT ROWNUMBER() OVER() AS Subject, T1.Drug AS StudyPeriod1, T2.Drug AS StudyPeriod2,

T3.Drug AS StudyPeriod3, T4.Drug AS StudyPeriod4

FROM DrugProduct T1, DrugProduct T2, DrugProduct T3, DrugProduct T4

WHERE (T1.Drug <> T2.Drug) AND (T1.Drug <> T3.Drug) AND

(T1.Drug <> T4.Drug) AND (T2.Drug <> T3.Drug) AND

(T2.Drug <> T4.Drug) AND (T3.Drug <> T4.Drug);

--A Latin Square is a subset of permutations and can be easily built by shifting

--your values one over in a row and moving the last value in the row to the first

--column. Each individual value occurs once in each column and row. For example;

-- 1,2,3,4 or A,B,C,D

-- 4,1,2,3 D,A,B,C

-- 3,4,1,2 C,D,A,B

-- 2,3,4,1 B,C,D,A

CREATE TABLE LatinSquare(DrugID1 CHAR(1), DrugID2 CHAR(1), DrugID3 CHAR(1), DrugID4 CHAR(1));

INSERT INTO LatinSquare VALUES('A', 'B', 'C', 'D')

,('D', 'A', 'B', 'C')

,('C', 'D', 'A', 'B')

,('B', 'C', 'D', 'A');

SELECT * FROM LatinSquare;

--Clean up.

DROP TABLE DrugProduct;

DROP TABLE LatinSquare;