USE tempdb

--Some examples of ranking qualities and quantities. Run the entire

--script and compare tables.

--By C. Eric Cashon

CREATE TABLE Results(

Color varchar(10),

Metal varchar(10),

Shape varchar(10),

Quantity int)

GO

--Load sample data.

SET NOCOUNT ON

INSERT INTO Results VALUES('Red', 'Titanium', 'Circle', 8)

INSERT INTO Results VALUES('Red', 'Iridium', 'Square', 8)

INSERT INTO Results VALUES('Blue', 'Iridium', 'Square', 6)

INSERT INTO Results VALUES('Red', 'Titanium', 'Triangle', 7)

INSERT INTO Results VALUES('Green', 'Aluminum', 'Triangle', 2)

INSERT INTO Results VALUES('Green', 'Titanium', 'Circle', 8)

INSERT INTO Results VALUES('Red', 'Aluminum', 'Circle', 2)

INSERT INTO Results VALUES('Blue', 'Iridium', 'Triangle', 4)

INSERT INTO Results VALUES('Blue', 'Iridium', 'Square', 7)

INSERT INTO Results VALUES('Red', 'Aluminum', 'Triangle', 6)

INSERT INTO Results VALUES('Green', 'Titanium', 'Square', 9)

INSERT INTO Results VALUES('Green', 'Titanium', 'Triangle', 4)

INSERT INTO Results VALUES('Blue', 'Aluminum', 'Circle', 1)

INSERT INTO Results VALUES('Red', 'Iridium', 'Square', 7)

INSERT INTO Results VALUES('Blue', 'Titanium', 'Circle', 5)

INSERT INTO Results VALUES('Green', 'Titanium', 'Circle', 2)

INSERT INTO Results VALUES('Red', 'Aluminum', 'Square', 6)

INSERT INTO Results VALUES('Red', 'Iridium', 'Triangle', 3)

INSERT INTO Results VALUES('Green', 'Titanium', 'Triangle', 9)

INSERT INTO Results VALUES('Blue', 'Iridium', 'Circle', 4)

INSERT INTO Results VALUES('Green', 'Aluminum', 'Triangle', 5)

INSERT INTO Results VALUES('Green', 'Iridium', 'Square', 1)

GO

--Combinations of qualities without zero cases.

SELECT Color, Metal, Shape, Sum(Quantity)SumOfValues

FROM Results GROUP BY Color, Metal, Shape ORDER BY SumOfValues DESC

--Get all the possible combinations of your qualitative values.

SELECT * INTO Combinations FROM

(SELECT TOP 100 PERCENT T1.Color, T2.Metal, T3.Shape FROM

Results T1 CROSS JOIN Results T2 CROSS JOIN Results T3

GROUP BY T1.Color, T2.Metal, T3.Shape

ORDER BY T1.Color, T2.Metal, T3.Shape) T4

GO

--Create a table to put every qualitative combination and the

--associated value. Include zero values also.

CREATE TABLE RankResults(

Color varchar(10),

Metal varchar(10),

Shape varchar(10),

SumOfValues int)

GO

--Open a cursor and sum values for each possible combination.

--The same as the first query but add combinations of qualities

--that add to zero.

DECLARE @Color varchar(10)

DECLARE @Metal varchar(10)

DECLARE @Shape varchar(10)

DECLARE @RowCount int

DECLARE ComboCursor CURSOR FAST_FORWARD FOR

SELECT * FROM Combinations

OPEN ComboCursor

FETCH NEXT FROM ComboCursor INTO @Color, @Metal, @Shape

WHILE @@FETCH_STATUS = 0

BEGIN

SET @RowCount = (SELECT COUNT(*) FROM Results

WHERE Color = @Color AND Metal = @Metal

AND Shape = @Shape)

IF @RowCount = 0

INSERT INTO RankResults VALUES(@Color, @Metal, @Shape, 0)

ELSE

INSERT INTO RankResults (Color, Metal, Shape, SumOfValues)

(SELECT T1.Color, T1.Metal, T1.Shape,

SUM(T1.Quantity) FROM Results T1

WHERE Color = @Color AND Metal = @Metal

AND Shape = @Shape GROUP BY Color, Metal, Shape)

FETCH NEXT FROM ComboCursor INTO @Color, @Metal, @Shape

END

CLOSE ComboCursor

DEALLOCATE ComboCursor

GO

--Return the values to compare with the first query.

SELECT * FROM RankResults ORDER BY SumOfValues DESC

GO

--Look for values associated with each individual quality.

CREATE TABLE AllQualities(

Qualities varchar(10))

CREATE TABLE SecondResults(

Qualities varchar(10),

SumValues int)

GO

--Put all the qualitative values in one column so that each can

--be ranked.

INSERT INTO AllQualities (Qualities)

(SELECT Color FROM Combinations GROUP BY Color)

INSERT INTO AllQualities (Qualities)

(SELECT Metal FROM Combinations GROUP BY Metal)

INSERT INTO AllQualities (Qualities)

(SELECT Shape FROM Combinations GROUP BY Shape)

GO

--Look at each value associated with each quality.

DECLARE @Quality varchar(10)

DECLARE QualCursor CURSOR FAST_FORWARD FOR

SELECT * FROM AllQualities

OPEN QualCursor

FETCH NEXT FROM QualCursor INTO @Quality

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO SecondResults(Qualities, SumValues)

(SELECT @Quality, SUM(Quantity) FROM Results

WHERE Color = @Quality OR Metal = @Quality OR Shape = @Quality

GROUP BY Quantity)

FETCH NEXT FROM QualCursor INTO @Quality

END

CLOSE QualCursor

DEALLOCATE QualCursor

--Find sum of values based on each individual quality.

SELECT Qualities, SUM(SumValues)Total FROM SecondResults

GROUP BY Qualities

ORDER BY Total DESC

--Find sum of values based on groups of qualities. Notice the

--values are all the same since the qualities are not independent.

SELECT 'Color' Quality, SUM(SumValues) Total FROM SecondResults

WHERE Qualities IN('Red', 'Green', 'Blue')

UNION

SELECT 'Metal', SUM(SumValues) Total FROM SecondResults

WHERE Qualities IN('Titanium', 'Iridium', 'Aluminum')

UNION

SELECT 'Shape', SUM(SumValues) Total FROM SecondResults

WHERE Qualities IN('Circle', 'Square', 'Triangle')

GO

--Clean up

DROP TABLE Results

DROP TABLE Combinations

DROP TABLE RankResults

DROP TABLE AllQualities

DROP TABLE SecondResults