8SDp4m1q

1-- Table of contents (2/3/4 Man, Mini/Full, Manual/Auto)
2-- 2 Man Mini Guildford, Manual Entry
3-- 2 Man Mini Guildford, Auto Entry
4-- 2 Man Full Guildford, Manual Entry
5-- 2 Man Full Guildford, Auto Entry
6-- 3 Man Mini Guildford, Manual Entry
7-- 3 Man Mini Guildford, Auto Entry
8-- 3 Man Full Guildford, Manual Entry
9-- 3 Man Full Guildford, Auto Entry
10-- 4 Man Mini Guildford, Manual Entry
11-- 4 Man Mini Guildford, Auto Entry
12-- 4 Man Full Guildford, Manual Entry
13-- 4 Man Full Guildford, Auto Entry
14
15-- 2 Man Mini Guildford, Manual Entry (enter NULL if you don't want to do an event)
16SET
17@personA = ,
18@333A = ,
19@222A = ,
20@444A = ,
21@555A = ,
22@333ohA = ,
23@clockA = ,
24@minxA = ,
25@pyramA = ,
26@sq1A = ,
27@skewbA = ,
28
29@personB = ,
30@333B = ,
31@222B = ,
32@444B = ,
33@555B = ,
34@333ohB = ,
35@clockB = ,
36@minxB = ,
37@pyramB = ,
38@sq1B = ,
39@skewbB = ;
40--
41
42
43DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
44CREATE TABLE oj_stats.Optimal_Relay_Help
45(personId TEXT, eventId TEXT, average INT);
46INSERT INTO oj_stats.Optimal_Relay_Help
47VALUES
48	(@personA, '333', @333A),
49	(@personA, '222', @222A),
50	(@personA, '444', @444A),
51	(@personA, '555', @555A),
52	(@personA, '333oh', @333ohA),
53	(@personA, 'clock', @clockA),
54	(@personA, 'minx', @minxA),
55	(@personA, 'pyram', @pyramA),
56	(@personA, 'sq1', @sq1A),
57	(@personA, 'skewb', @skewbA),
58	(@personB, '333', @333B),
59	(@personB, '222', @222B),
60	(@personB, '444', @444B),
61	(@personB, '555', @555B),
62	(@personB, '333oh', @333ohB),
63	(@personB, 'clock', @clockB),
64	(@personB, 'minx', @minxB),
65	(@personB, 'pyram', @pyramB),
66	(@personB, 'sq1', @sq1B),
67	(@personB, 'skewb', @skewbB);
68
69SET @pAt = 0, @pBt = 0;
70DROP TABLE oj_stats.Optimal_Relay;
71CREATE TABLE oj_stats.Optimal_Relay
72SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
73	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
74	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
75	(CASE WHEN @pAt > @pBt THEN @pAt ELSE @pBt END) 'Total'
76FROM
77	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
78CROSS JOIN
79	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
80CROSS JOIN
81	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
82CROSS JOIN
83	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
84CROSS JOIN
85	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
86CROSS JOIN
87	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
88CROSS JOIN
89	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
90CROSS JOIN
91	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
92CROSS JOIN
93	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
94CROSS JOIN
95	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
96ORDER BY Total ASC, personAtotal ASC, personBTotal ASC;
97
98
99
100-- 2 Man Mini Guildford, Auto Entry (enter personId, result is automatically NULL if you don't have an average)
101SET
102@personA = '2016WHEA01',
103@personB = '2017GOLD02';
104--
105
106
107SET
108@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
109@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
110@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
111@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
112@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
113@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
114@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
115@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
116@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
117@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
118@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
119@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
120@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
121@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
122@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
123@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
124@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
125@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
126@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
127@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a');
128
129DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
130CREATE TABLE oj_stats.Optimal_Relay_Help
131(personId TEXT, eventId TEXT, average INT);
132INSERT INTO oj_stats.Optimal_Relay_Help
133VALUES
134	(@personA, '333', @333A),
135	(@personA, '222', @222A),
136	(@personA, '444', @444A),
137	(@personA, '555', @555A),
138	(@personA, '333oh', @333ohA),
139	(@personA, 'clock', @clockA),
140	(@personA, 'minx', @minxA),
141	(@personA, 'pyram', @pyramA),
142	(@personA, 'sq1', @sq1A),
143	(@personA, 'skewb', @skewbA),
144	(@personB, '333', @333B),
145	(@personB, '222', @222B),
146	(@personB, '444', @444B),
147	(@personB, '555', @555B),
148	(@personB, '333oh', @333ohB),
149	(@personB, 'clock', @clockB),
150	(@personB, 'minx', @minxB),
151	(@personB, 'pyram', @pyramB),
152	(@personB, 'sq1', @sq1B),
153	(@personB, 'skewb', @skewbB);
154
155SET @pAt = 0, @pBt = 0;
156DROP TABLE oj_stats.Optimal_Relay;
157CREATE TABLE oj_stats.Optimal_Relay
158SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
159	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
160	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
161	(CASE WHEN @pAt > @pBt THEN @pAt ELSE @pBt END) 'Total'
162FROM
163	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
164CROSS JOIN
165	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
166CROSS JOIN
167	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
168CROSS JOIN
169	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
170CROSS JOIN
171	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
172CROSS JOIN
173	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
174CROSS JOIN
175	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
176CROSS JOIN
177	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
178CROSS JOIN
179	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
180CROSS JOIN
181	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
182ORDER BY Total ASC, personAtotal ASC, personBTotal ASC;
183
184
185
186-- 2 Man Full Guildford, Manual Entry (enter NULL if you don't want to do an event)
187SET
188@personA = ,
189@333A = ,
190@222A = ,
191@444A = ,
192@555A = ,
193@666A = ,
194@777A = ,
195@333ftA = ,
196@333ohA = ,
197@clockA = ,
198@minxA = ,
199@pyramA = ,
200@sq1A = ,
201@skewbA = ,
202
203@personB = ,
204@333B = ,
205@222B = ,
206@444B = ,
207@555B = ,
208@666B = ,
209@777B = ,
210@333ftB = ,
211@333ohB = ,
212@clockB = ,
213@minxB = ,
214@pyramB = ,
215@sq1B = ,
216@skewbB = ;
217--
218
219
220DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
221CREATE TABLE oj_stats.Optimal_Relay_Help
222(personId TEXT, eventId TEXT, average INT);
223INSERT INTO oj_stats.Optimal_Relay_Help
224VALUES
225	(@personA, '333', @333A),
226	(@personA, '222', @222A),
227	(@personA, '444', @444A),
228	(@personA, '555', @555A),
229	(@personA, '666', @666A),
230	(@personA, '777', @777A),
231	(@personA, '333ft', @333ftA),
232	(@personA, '333oh', @333ohA),
233	(@personA, 'clock', @clockA),
234	(@personA, 'minx', @minxA),
235	(@personA, 'pyram', @pyramA),
236	(@personA, 'sq1', @sq1A),
237	(@personA, 'skewb', @skewbA),
238	(@personB, '333', @333B),
239	(@personB, '222', @222B),
240	(@personB, '444', @444B),
241	(@personB, '555', @555B),
242	(@personB, '666', @666B),
243	(@personB, '777', @777B),
244	(@personB, '333ft', @333ftB),
245	(@personB, '333oh', @333ohB),
246	(@personB, 'clock', @clockB),
247	(@personB, 'minx', @minxB),
248	(@personB, 'pyram', @pyramB),
249	(@personB, 'sq1', @sq1B),
250	(@personB, 'skewb', @skewbB);
251
252SET @pAt = 0, @pBt = 0;
253DROP TABLE oj_stats.Optimal_Relay;
254CREATE TABLE oj_stats.Optimal_Relay
255SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
256	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
257	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
258	(CASE WHEN @pAt > @pBt THEN @pAt ELSE @pBt END) 'Total'
259FROM
260	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
261CROSS JOIN
262	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
263CROSS JOIN
264	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
265CROSS JOIN
266	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
267CROSS JOIN
268	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
269CROSS JOIN
270	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
271CROSS JOIN
272	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
273CROSS JOIN
274	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
275CROSS JOIN
276	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
277CROSS JOIN
278	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
279CROSS JOIN
280	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
281CROSS JOIN
282	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
283CROSS JOIN
284	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
285ORDER BY Total ASC, personAtotal ASC, personBTotal ASC;
286
287
288
289-- 2 Man Full Guildford, Auto Entry (result is automatically NULL if you don't have an average)
290SET
291@personA = ,
292@personB = ;
293--
294
295
296SET
297@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
298@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
299@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
300@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
301@666A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '666' AND format = 'a'),
302@777A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '777' AND format = 'a'),
303@333ftA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333ft' AND format = 'a'),
304@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
305@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
306@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
307@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
308@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
309@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
310@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
311@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
312@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
313@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
314@666B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '666' AND format = 'a'),
315@777B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '777' AND format = 'a'),
316@333ftB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333ft' AND format = 'a'),
317@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
318@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
319@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
320@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
321@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
322@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a');
323
324DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
325CREATE TABLE oj_stats.Optimal_Relay_Help
326(personId TEXT, eventId TEXT, average INT);
327INSERT INTO oj_stats.Optimal_Relay_Help
328VALUES
329	(@personA, '333', @333A),
330	(@personA, '222', @222A),
331	(@personA, '444', @444A),
332	(@personA, '555', @555A),
333	(@personA, '666', @666A),
334	(@personA, '777', @777A),
335	(@personA, '333ft', @333ftA),
336	(@personA, '333oh', @333ohA),
337	(@personA, 'clock', @clockA),
338	(@personA, 'minx', @minxA),
339	(@personA, 'pyram', @pyramA),
340	(@personA, 'sq1', @sq1A),
341	(@personA, 'skewb', @skewbA),
342	(@personB, '333', @333B),
343	(@personB, '222', @222B),
344	(@personB, '444', @444B),
345	(@personB, '555', @555B),
346	(@personB, '666', @666B),
347	(@personB, '777', @777B),
348	(@personB, '333ft', @333ftB),
349	(@personB, '333oh', @333ohB),
350	(@personB, 'clock', @clockB),
351	(@personB, 'minx', @minxB),
352	(@personB, 'pyram', @pyramB),
353	(@personB, 'sq1', @sq1B),
354	(@personB, 'skewb', @skewbB);
355
356SET @pAt = 0, @pBt = 0;
357DROP TABLE oj_stats.Optimal_Relay;
358CREATE TABLE oj_stats.Optimal_Relay
359SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
360	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
361	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
362	(CASE WHEN @pAt > @pBt THEN @pAt ELSE @pBt END) 'Total'
363FROM
364	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
365CROSS JOIN
366	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
367CROSS JOIN
368	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
369CROSS JOIN
370	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
371CROSS JOIN
372	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
373CROSS JOIN
374	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
375CROSS JOIN
376	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
377CROSS JOIN
378	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
379CROSS JOIN
380	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
381CROSS JOIN
382	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
383CROSS JOIN
384	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
385CROSS JOIN
386	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
387CROSS JOIN
388	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
389ORDER BY Total ASC, personAtotal ASC, personBTotal ASC;
390
391
392
393-- 3 Man Mini Guildford, Manual Entry (enter NULL if you don't want to do an event)
394SET
395@personA = ,
396@333A = ,
397@222A = ,
398@444A = ,
399@555A = ,
400@333ohA = ,
401@clockA = ,
402@minxA = ,
403@pyramA = ,
404@sq1A = ,
405@skewbA = ,
406
407@personB = ,
408@333B = ,
409@222B = ,
410@444B = ,
411@555B = ,
412@333ohB = ,
413@clockB = ,
414@minxB = ,
415@pyramB = ,
416@sq1B = ,
417@skewbB = ,
418
419@personC = ,
420@333C = ,
421@222C = ,
422@444C = ,
423@555C = ,
424@333ohC = ,
425@clockC = ,
426@minxC = ,
427@pyramC = ,
428@sq1C = ,
429@skewbC = ;
430--
431
432
433DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
434CREATE TABLE oj_stats.Optimal_Relay_Help
435(personId TEXT, eventId TEXT, average INT);
436INSERT INTO oj_stats.Optimal_Relay_Help
437VALUES
438	(@personA, '333', @333A),
439	(@personA, '222', @222A),
440	(@personA, '444', @444A),
441	(@personA, '555', @555A),
442	(@personA, '333oh', @333ohA),
443	(@personA, 'clock', @clockA),
444	(@personA, 'minx', @minxA),
445	(@personA, 'pyram', @pyramA),
446	(@personA, 'sq1', @sq1A),
447	(@personA, 'skewb', @skewbA),
448	(@personB, '333', @333B),
449	(@personB, '222', @222B),
450	(@personB, '444', @444B),
451	(@personB, '555', @555B),
452	(@personB, '333oh', @333ohB),
453	(@personB, 'clock', @clockB),
454	(@personB, 'minx', @minxB),
455	(@personB, 'pyram', @pyramB),
456	(@personB, 'sq1', @sq1B),
457	(@personB, 'skewb', @skewbB),
458	(@personC, '333', @333C),
459	(@personC, '222', @222C),
460	(@personC, '444', @444C),
461	(@personC, '555', @555C),
462	(@personC, '333oh', @333ohC),
463	(@personC, 'clock', @clockC),
464	(@personC, 'minx', @minxC),
465	(@personC, 'pyram', @pyramC),
466	(@personC, 'sq1', @sq1C),
467	(@personC, 'skewb', @skewbC);
468
469SET @pAt = 0, @pBt = 0, @pCt = 0;
470DROP TABLE oj_stats.Optimal_Relay;
471CREATE TABLE oj_stats.Optimal_Relay
472SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
473	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
474	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
475	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
476	(CASE WHEN @pAt > @pBt AND @pAt > @pCt THEN @pAt WHEN @pBt > @pCt THEN @pBt ELSE @pCt END) 'Total'
477FROM
478	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
479CROSS JOIN
480	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
481CROSS JOIN
482	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
483CROSS JOIN
484	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
485CROSS JOIN
486	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
487CROSS JOIN
488	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
489CROSS JOIN
490	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
491CROSS JOIN
492	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
493CROSS JOIN
494	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
495CROSS JOIN
496	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
497ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC;
498
499
500
501-- 3 Man Mini Guildford, Auto Entry (result is automatically NULL if you don't have an average)
502SET
503@personA = ,
504@personB = ,
505@personC = ;
506--
507
508
509SET
510@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
511@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
512@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
513@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
514@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
515@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
516@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
517@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
518@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
519@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
520@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
521@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
522@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
523@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
524@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
525@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
526@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
527@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
528@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
529@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a'),
530@333C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333' AND format = 'a'),
531@222C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '222' AND format = 'a'),
532@444C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '444' AND format = 'a'),
533@555C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '555' AND format = 'a'),
534@333ohC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333oh' AND format = 'a'),
535@clockC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'clock' AND format = 'a'),
536@minxC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'minx' AND format = 'a'),
537@pyramC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'pyram' AND format = 'a'),
538@sq1C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'sq1' AND format = 'a'),
539@skewbC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'skewb' AND format = 'a');
540
541DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
542CREATE TABLE oj_stats.Optimal_Relay_Help
543(personId TEXT, eventId TEXT, average INT);
544INSERT INTO oj_stats.Optimal_Relay_Help
545VALUES
546	(@personA, '333', @333A),
547	(@personA, '222', @222A),
548	(@personA, '444', @444A),
549	(@personA, '555', @555A),
550	(@personA, '333oh', @333ohA),
551	(@personA, 'clock', @clockA),
552	(@personA, 'minx', @minxA),
553	(@personA, 'pyram', @pyramA),
554	(@personA, 'sq1', @sq1A),
555	(@personA, 'skewb', @skewbA),
556	(@personB, '333', @333B),
557	(@personB, '222', @222B),
558	(@personB, '444', @444B),
559	(@personB, '555', @555B),
560	(@personB, '333oh', @333ohB),
561	(@personB, 'clock', @clockB),
562	(@personB, 'minx', @minxB),
563	(@personB, 'pyram', @pyramB),
564	(@personB, 'sq1', @sq1B),
565	(@personB, 'skewb', @skewbB),
566	(@personC, '333', @333C),
567	(@personC, '222', @222C),
568	(@personC, '444', @444C),
569	(@personC, '555', @555C),
570	(@personC, '333oh', @333ohC),
571	(@personC, 'clock', @clockC),
572	(@personC, 'minx', @minxC),
573	(@personC, 'pyram', @pyramC),
574	(@personC, 'sq1', @sq1C),
575	(@personC, 'skewb', @skewbC);
576
577SET @pAt = 0, @pBt = 0, @pCt = 0;
578DROP TABLE oj_stats.Optimal_Relay;
579CREATE TABLE oj_stats.Optimal_Relay
580SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
581	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
582	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
583	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
584	(CASE WHEN @pAt > @pBt AND @pAt > @pCt THEN @pAt WHEN @pBt > @pCt THEN @pBt ELSE @pCt END) 'Total'
585FROM
586	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
587CROSS JOIN
588	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
589CROSS JOIN
590	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
591CROSS JOIN
592	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
593CROSS JOIN
594	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
595CROSS JOIN
596	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
597CROSS JOIN
598	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
599CROSS JOIN
600	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
601CROSS JOIN
602	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
603CROSS JOIN
604	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
605ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC;
606
607
608
609-- 3 Man Full Guildford, Manual Entry (enter NULL if you don't want to do an event)
610SET
611@personA = ,
612@333A = ,
613@222A = ,
614@444A = ,
615@555A = ,
616@666A = ,
617@777A = ,
618@333ftA = ,
619@333ohA = ,
620@clockA = ,
621@minxA = ,
622@pyramA = ,
623@sq1A = ,
624@skewbA = ,
625
626@personB = ,
627@333B = ,
628@222B = ,
629@444B = ,
630@555B = ,
631@666B = ,
632@777B = ,
633@333ftB = ,
634@333ohB = ,
635@clockB = ,
636@minxB = ,
637@pyramB = ,
638@sq1B = ,
639@skewbB = ,
640
641@personC = ,
642@333C = ,
643@222C = ,
644@444C = ,
645@555C = ,
646@666C = ,
647@777C = ,
648@333ftC = ,
649@333ohC = ,
650@clockC = ,
651@minxC = ,
652@pyramC = ,
653@sq1C = ,
654@skewbC = ;
655--
656
657
658DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
659CREATE TABLE oj_stats.Optimal_Relay_Help
660(personId TEXT, eventId TEXT, average INT);
661INSERT INTO oj_stats.Optimal_Relay_Help
662VALUES
663	(@personA, '333', @333A),
664	(@personA, '222', @222A),
665	(@personA, '444', @444A),
666	(@personA, '555', @555A),
667	(@personA, '666', @666A),
668	(@personA, '777', @777A),
669	(@personA, '333ft', @333ftA),
670	(@personA, '333oh', @333ohA),
671	(@personA, 'clock', @clockA),
672	(@personA, 'minx', @minxA),
673	(@personA, 'pyram', @pyramA),
674	(@personA, 'sq1', @sq1A),
675	(@personA, 'skewb', @skewbA),
676	(@personB, '333', @333B),
677	(@personB, '222', @222B),
678	(@personB, '444', @444B),
679	(@personB, '555', @555B),
680	(@personB, '666', @666B),
681	(@personB, '777', @777B),
682	(@personB, '333ft', @333ftB),
683	(@personB, '333oh', @333ohB),
684	(@personB, 'clock', @clockB),
685	(@personB, 'minx', @minxB),
686	(@personB, 'pyram', @pyramB),
687	(@personB, 'sq1', @sq1B),
688	(@personB, 'skewb', @skewbB),
689	(@personC, '333', @333C),
690	(@personC, '222', @222C),
691	(@personC, '444', @444C),
692	(@personC, '555', @555C),
693	(@personC, '666', @666C),
694	(@personC, '777', @777C),
695	(@personC, '333ft', @333ftC),
696	(@personC, '333oh', @333ohC),
697	(@personC, 'clock', @clockC),
698	(@personC, 'minx', @minxC),
699	(@personC, 'pyram', @pyramC),
700	(@personC, 'sq1', @sq1C),
701	(@personC, 'skewb', @skewbC);
702
703SET @pAt = 0, @pBt = 0, @pCt = 0;
704DROP TABLE oj_stats.Optimal_Relay;
705CREATE TABLE oj_stats.Optimal_Relay
706SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
707	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
708	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
709	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(x.personId = @personC, x.average, 0)+IF(y.personId = @personC, y.average, 0)+IF(z.personId = @personC, z.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
710	(CASE WHEN @pAt > @pBt AND @pAt > @pCt THEN @pAt WHEN @pBt > @pCt THEN @pBt ELSE @pCt END) 'Total'
711FROM
712	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
713CROSS JOIN
714	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
715CROSS JOIN
716	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
717CROSS JOIN
718	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
719CROSS JOIN
720	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
721CROSS JOIN
722	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
723CROSS JOIN
724	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
725CROSS JOIN
726	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
727CROSS JOIN
728	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
729CROSS JOIN
730	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
731CROSS JOIN
732	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
733CROSS JOIN
734	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
735CROSS JOIN
736	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
737ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC;
738
739
740
741-- 3 Man Full Guildford, Auto Entry (result is automatically NULL if you don't have an average)
742SET
743@personA = '2016WHEA01',
744@personB = '2017GOLD02',
745@personC = '2015HAMM01';
746--
747
748
749SET
750@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
751@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
752@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
753@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
754@666A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '666' AND format = 'a'),
755@777A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '777' AND format = 'a'),
756@333ftA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333ft' AND format = 'a'),
757@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
758@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
759@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
760@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
761@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
762@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
763@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
764@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
765@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
766@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
767@666B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '666' AND format = 'a'),
768@777B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '777' AND format = 'a'),
769@333ftB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333ft' AND format = 'a'),
770@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
771@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
772@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
773@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
774@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
775@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a'),
776@333C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333' AND format = 'a'),
777@222C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '222' AND format = 'a'),
778@444C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '444' AND format = 'a'),
779@555C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '555' AND format = 'a'),
780@666C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '666' AND format = 'a'),
781@777C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '777' AND format = 'a'),
782@333ftC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333ft' AND format = 'a'),
783@333ohC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333oh' AND format = 'a'),
784@clockC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'clock' AND format = 'a'),
785@minxC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'minx' AND format = 'a'),
786@pyramC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'pyram' AND format = 'a'),
787@sq1C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'sq1' AND format = 'a'),
788@skewbC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'skewb' AND format = 'a');
789
790DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
791CREATE TABLE oj_stats.Optimal_Relay_Help
792(personId TEXT, eventId TEXT, average INT);
793INSERT INTO oj_stats.Optimal_Relay_Help
794VALUES
795	(@personA, '333', @333A),
796	(@personA, '222', @222A),
797	(@personA, '444', @444A),
798	(@personA, '555', @555A),
799	(@personA, '666', @666A),
800	(@personA, '777', @777A),
801	(@personA, '333ft', @333ftA),
802	(@personA, '333oh', @333ohA),
803	(@personA, 'clock', @clockA),
804	(@personA, 'minx', @minxA),
805	(@personA, 'pyram', @pyramA),
806	(@personA, 'sq1', @sq1A),
807	(@personA, 'skewb', @skewbA),
808	(@personB, '333', @333B),
809	(@personB, '222', @222B),
810	(@personB, '444', @444B),
811	(@personB, '555', @555B),
812	(@personB, '666', @666B),
813	(@personB, '777', @777B),
814	(@personB, '333ft', @333ftB),
815	(@personB, '333oh', @333ohB),
816	(@personB, 'clock', @clockB),
817	(@personB, 'minx', @minxB),
818	(@personB, 'pyram', @pyramB),
819	(@personB, 'sq1', @sq1B),
820	(@personB, 'skewb', @skewbB),
821	(@personC, '333', @333C),
822	(@personC, '222', @222C),
823	(@personC, '444', @444C),
824	(@personC, '555', @555C),
825	(@personC, '666', @666C),
826	(@personC, '777', @777C),
827	(@personC, '333ft', @333ftC),
828	(@personC, '333oh', @333ohC),
829	(@personC, 'clock', @clockC),
830	(@personC, 'minx', @minxC),
831	(@personC, 'pyram', @pyramC),
832	(@personC, 'sq1', @sq1C),
833	(@personC, 'skewb', @skewbC);
834
835SET @pAt = 0, @pBt = 0, @pCt = 0;
836DROP TABLE oj_stats.Optimal_Relay;
837CREATE TABLE oj_stats.Optimal_Relay
838SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
839	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
840	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
841	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(x.personId = @personC, x.average, 0)+IF(y.personId = @personC, y.average, 0)+IF(z.personId = @personC, z.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
842	(CASE WHEN @pAt > @pBt AND @pAt > @pCt THEN @pAt WHEN @pBt > @pCt THEN @pBt ELSE @pCt END) 'Total'
843FROM
844	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
845CROSS JOIN
846	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
847CROSS JOIN
848	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
849CROSS JOIN
850	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
851CROSS JOIN
852	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
853CROSS JOIN
854	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
855CROSS JOIN
856	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
857CROSS JOIN
858	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
859CROSS JOIN
860	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
861CROSS JOIN
862	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
863CROSS JOIN
864	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
865CROSS JOIN
866	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
867CROSS JOIN
868	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
869ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC;
870
871
872
873-- 4 Man Mini Guildford, Manual Entry (enter NULL if you don't want to do an event)
874SET
875@personA = ,
876@333A = ,
877@222A = ,
878@444A = ,
879@555A = ,
880@333ohA = ,
881@clockA = ,
882@minxA = ,
883@pyramA = ,
884@sq1A = ,
885@skewbA = ,
886
887@personB = ,
888@333B = ,
889@222B = ,
890@444B = ,
891@555B = ,
892@333ohB = ,
893@clockB = ,
894@minxB = ,
895@pyramB = ,
896@sq1B = ,
897@skewbB = ,
898
899@personC = ,
900@333C = ,
901@222C = ,
902@444C = ,
903@555C = ,
904@333ohC = ,
905@clockC = ,
906@minxC = ,
907@pyramC = ,
908@sq1C = ,
909@skewbC = ,
910
911@personD = ,
912@333D = ,
913@222D = ,
914@444D = ,
915@555D = ,
916@333ohD = ,
917@clockD = ,
918@minxD = ,
919@pyramD = ,
920@sq1D = ,
921@skewbD = ;
922--
923
924
925DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
926CREATE TABLE oj_stats.Optimal_Relay_Help
927(personId TEXT, eventId TEXT, average INT);
928INSERT INTO oj_stats.Optimal_Relay_Help
929VALUES
930	(@personA, '333', @333A),
931	(@personA, '222', @222A),
932	(@personA, '444', @444A),
933	(@personA, '555', @555A),
934	(@personA, '333oh', @333ohA),
935	(@personA, 'clock', @clockA),
936	(@personA, 'minx', @minxA),
937	(@personA, 'pyram', @pyramA),
938	(@personA, 'sq1', @sq1A),
939	(@personA, 'skewb', @skewbA),
940	(@personB, '333', @333B),
941	(@personB, '222', @222B),
942	(@personB, '444', @444B),
943	(@personB, '555', @555B),
944	(@personB, '333oh', @333ohB),
945	(@personB, 'clock', @clockB),
946	(@personB, 'minx', @minxB),
947	(@personB, 'pyram', @pyramB),
948	(@personB, 'sq1', @sq1B),
949	(@personB, 'skewb', @skewbB),
950	(@personC, '333', @333C),
951	(@personC, '222', @222C),
952	(@personC, '444', @444C),
953	(@personC, '555', @555C),
954	(@personC, '333oh', @333ohC),
955	(@personC, 'clock', @clockC),
956	(@personC, 'minx', @minxC),
957	(@personC, 'pyram', @pyramC),
958	(@personC, 'sq1', @sq1C),
959	(@personC, 'skewb', @skewbC),
960	(@personD, '333', @333D),
961	(@personD, '222', @222D),
962	(@personD, '444', @444D),
963	(@personD, '555', @555D),
964	(@personD, '333oh', @333ohD),
965	(@personD, 'clock', @clockD),
966	(@personD, 'minx', @minxD),
967	(@personD, 'pyram', @pyramD),
968	(@personD, 'sq1', @sq1D),
969	(@personD, 'skewb', @skewbD);
970
971SET @pAt = 0, @pBt = 0, @pCt = 0, @pDt = 0;
972DROP TABLE oj_stats.Optimal_Relay;
973CREATE TABLE oj_stats.Optimal_Relay
974SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
975	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
976	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
977	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
978	@pDt := IF(a.personId = @personD, a.average, 0)+IF(b.personId = @personD, b.average, 0)+IF(c.personId = @personD, c.average, 0)+IF(d.personId = @personD, d.average, 0)+IF(e.personId = @personD, e.average, 0)+IF(f.personId = @personD, f.average, 0)+IF(g.personId = @personD, g.average, 0)+IF(h.personId = @personD, h.average, 0)+IF(i.personId = @personD, i.average, 0)+IF(j.personId = @personD, j.average, 0) 'personDTotal',
979	(CASE WHEN @pAt > @pBt AND @pAt > @pCt AND @pAt > @pDt THEN @pAt WHEN @pBt > @pCt AND @pBt > @pDt THEN @pBt WHEN @pCt > @pDt THEN @pCt ELSE @pDt END) 'Total'
980FROM
981	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
982CROSS JOIN
983	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
984CROSS JOIN
985	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
986CROSS JOIN
987	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
988CROSS JOIN
989	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
990CROSS JOIN
991	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
992CROSS JOIN
993	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
994CROSS JOIN
995	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
996CROSS JOIN
997	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
998CROSS JOIN
999	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
1000ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC, personDTotal ASC;
1001
1002
1003
1004-- 4 Man Mini Guildford, Auto Entry (result is automatically NULL if you don't have an average)
1005SET
1006@personA = ,
1007@personB = ,
1008@personC = ,
1009@personD = ;
1010--
1011
1012
1013SET
1014@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
1015@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
1016@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
1017@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
1018@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
1019@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
1020@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
1021@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
1022@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
1023@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
1024@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
1025@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
1026@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
1027@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
1028@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
1029@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
1030@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
1031@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
1032@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
1033@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a'),
1034@333C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333' AND format = 'a'),
1035@222C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '222' AND format = 'a'),
1036@444C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '444' AND format = 'a'),
1037@555C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '555' AND format = 'a'),
1038@333ohC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333oh' AND format = 'a'),
1039@clockC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'clock' AND format = 'a'),
1040@minxC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'minx' AND format = 'a'),
1041@pyramC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'pyram' AND format = 'a'),
1042@sq1C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'sq1' AND format = 'a'),
1043@skewbC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'skewb' AND format = 'a'),
1044@333D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '333' AND format = 'a'),
1045@222D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '222' AND format = 'a'),
1046@444D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '444' AND format = 'a'),
1047@555D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '555' AND format = 'a'),
1048@333ohD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '333oh' AND format = 'a'),
1049@clockD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'clock' AND format = 'a'),
1050@minxD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'minx' AND format = 'a'),
1051@pyramD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'pyram' AND format = 'a'),
1052@sq1D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'sq1' AND format = 'a'),
1053@skewbD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'skewb' AND format = 'a');
1054
1055DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
1056CREATE TABLE oj_stats.Optimal_Relay_Help
1057(personId TEXT, eventId TEXT, average INT);
1058INSERT INTO oj_stats.Optimal_Relay_Help
1059VALUES
1060	(@personA, '333', @333A),
1061	(@personA, '222', @222A),
1062	(@personA, '444', @444A),
1063	(@personA, '555', @555A),
1064	(@personA, '333oh', @333ohA),
1065	(@personA, 'clock', @clockA),
1066	(@personA, 'minx', @minxA),
1067	(@personA, 'pyram', @pyramA),
1068	(@personA, 'sq1', @sq1A),
1069	(@personA, 'skewb', @skewbA),
1070	(@personB, '333', @333B),
1071	(@personB, '222', @222B),
1072	(@personB, '444', @444B),
1073	(@personB, '555', @555B),
1074	(@personB, '333oh', @333ohB),
1075	(@personB, 'clock', @clockB),
1076	(@personB, 'minx', @minxB),
1077	(@personB, 'pyram', @pyramB),
1078	(@personB, 'sq1', @sq1B),
1079	(@personB, 'skewb', @skewbB),
1080	(@personC, '333', @333C),
1081	(@personC, '222', @222C),
1082	(@personC, '444', @444C),
1083	(@personC, '555', @555C),
1084	(@personC, '333oh', @333ohC),
1085	(@personC, 'clock', @clockC),
1086	(@personC, 'minx', @minxC),
1087	(@personC, 'pyram', @pyramC),
1088	(@personC, 'sq1', @sq1C),
1089	(@personC, 'skewb', @skewbC),
1090	(@personD, '333', @333D),
1091	(@personD, '222', @222D),
1092	(@personD, '444', @444D),
1093	(@personD, '555', @555D),
1094	(@personD, '333oh', @333ohD),
1095	(@personD, 'clock', @clockD),
1096	(@personD, 'minx', @minxD),
1097	(@personD, 'pyram', @pyramD),
1098	(@personD, 'sq1', @sq1D),
1099	(@personD, 'skewb', @skewbD);
1100
1101SET @pAt = 0, @pBt = 0, @pCt = 0, @pDt = 0;
1102DROP TABLE oj_stats.Optimal_Relay;
1103CREATE TABLE oj_stats.Optimal_Relay
1104SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
1105	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
1106	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
1107	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
1108	@pDt := IF(a.personId = @personD, a.average, 0)+IF(b.personId = @personD, b.average, 0)+IF(c.personId = @personD, c.average, 0)+IF(d.personId = @personD, d.average, 0)+IF(e.personId = @personD, e.average, 0)+IF(f.personId = @personD, f.average, 0)+IF(g.personId = @personD, g.average, 0)+IF(h.personId = @personD, h.average, 0)+IF(i.personId = @personD, i.average, 0)+IF(j.personId = @personD, j.average, 0) 'personDTotal',
1109	(CASE WHEN @pAt > @pBt AND @pAt > @pCt AND @pAt > @pDt THEN @pAt WHEN @pBt > @pCt AND @pBt > @pDt THEN @pBt WHEN @pCt > @pDt THEN @pCt ELSE @pDt END) 'Total'
1110FROM
1111	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
1112CROSS JOIN
1113	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
1114CROSS JOIN
1115	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
1116CROSS JOIN
1117	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
1118CROSS JOIN
1119	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
1120CROSS JOIN
1121	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
1122CROSS JOIN
1123	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
1124CROSS JOIN
1125	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
1126CROSS JOIN
1127	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
1128CROSS JOIN
1129	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
1130ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC, personDTotal ASC;
1131
1132
1133
1134-- 4 Man Full Guildford, Manual Entry (enter NULL if you don't want to do an event)
1135SET
1136@personA = ,
1137@333A = ,
1138@222A = ,
1139@444A = ,
1140@555A = ,
1141@666A = ,
1142@777A = ,
1143@333ftA = ,
1144@333ohA = ,
1145@clockA = ,
1146@minxA = ,
1147@pyramA = ,
1148@sq1A = ,
1149@skewbA = ,
1150
1151@personB = ,
1152@333B = ,
1153@222B = ,
1154@444B = ,
1155@555B = ,
1156@666B = ,
1157@777B = ,
1158@333ftB = ,
1159@333ohB = ,
1160@clockB = ,
1161@minxB = ,
1162@pyramB = ,
1163@sq1B = ,
1164@skewbB = ,
1165
1166@personC = ,
1167@333C = ,
1168@222C = ,
1169@444C = ,
1170@555C = ,
1171@666C = ,
1172@777C = ,
1173@333ftC = ,
1174@333ohC = ,
1175@clockC = ,
1176@minxC = ,
1177@pyramC = ,
1178@sq1C = ,
1179@skewbC = ,
1180
1181@personD = ,
1182@333D = ,
1183@222D = ,
1184@444D = ,
1185@555D = ,
1186@666D = ,
1187@777D = ,
1188@333ftD = ,
1189@333ohD = ,
1190@clockD = ,
1191@minxD = ,
1192@pyramD = ,
1193@sq1D = ,
1194@skewbD = ;
1195--
1196
1197
1198DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
1199CREATE TABLE oj_stats.Optimal_Relay_Help
1200(personId TEXT, eventId TEXT, average INT);
1201INSERT INTO oj_stats.Optimal_Relay_Help
1202VALUES
1203	(@personA, '333', @333A),
1204	(@personA, '222', @222A),
1205	(@personA, '444', @444A),
1206	(@personA, '555', @555A),
1207	(@personA, '666', @666A),
1208	(@personA, '777', @777A),
1209	(@personA, '333ft', @333ftA),
1210	(@personA, '333oh', @333ohA),
1211	(@personA, 'clock', @clockA),
1212	(@personA, 'minx', @minxA),
1213	(@personA, 'pyram', @pyramA),
1214	(@personA, 'sq1', @sq1A),
1215	(@personA, 'skewb', @skewbA),
1216	(@personB, '333', @333B),
1217	(@personB, '222', @222B),
1218	(@personB, '444', @444B),
1219	(@personB, '555', @555B),
1220	(@personB, '666', @666B),
1221	(@personB, '777', @777B),
1222	(@personB, '333ft', @333ftB),
1223	(@personB, '333oh', @333ohB),
1224	(@personB, 'clock', @clockB),
1225	(@personB, 'minx', @minxB),
1226	(@personB, 'pyram', @pyramB),
1227	(@personB, 'sq1', @sq1B),
1228	(@personB, 'skewb', @skewbB),
1229	(@personC, '333', @333C),
1230	(@personC, '222', @222C),
1231	(@personC, '444', @444C),
1232	(@personC, '555', @555C),
1233	(@personC, '666', @666C),
1234	(@personC, '777', @777C),
1235	(@personC, '333ft', @333ftC),
1236	(@personC, '333oh', @333ohC),
1237	(@personC, 'clock', @clockC),
1238	(@personC, 'minx', @minxC),
1239	(@personC, 'pyram', @pyramC),
1240	(@personC, 'sq1', @sq1C),
1241	(@personC, 'skewb', @skewbC),
1242	(@personD, '333', @333D),
1243	(@personD, '222', @222D),
1244	(@personD, '444', @444D),
1245	(@personD, '555', @555D),
1246	(@personD, '666', @666D),
1247	(@personD, '777', @777D),
1248	(@personD, '333ft', @333ftD),
1249	(@personD, '333oh', @333ohD),
1250	(@personD, 'clock', @clockD),
1251	(@personD, 'minx', @minxD),
1252	(@personD, 'pyram', @pyramD),
1253	(@personD, 'sq1', @sq1D),
1254	(@personD, 'skewb', @skewbD);
1255
1256SET @pAt = 0, @pBt = 0, @pCt = 0, @pDt = 0;
1257DROP TABLE oj_stats.Optimal_Relay;
1258CREATE TABLE oj_stats.Optimal_Relay
1259SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
1260	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
1261	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
1262	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(x.personId = @personC, x.average, 0)+IF(y.personId = @personC, y.average, 0)+IF(z.personId = @personC, z.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
1263	@pDt := IF(a.personId = @personD, a.average, 0)+IF(b.personId = @personD, b.average, 0)+IF(c.personId = @personD, c.average, 0)+IF(d.personId = @personD, d.average, 0)+IF(x.personId = @personD, x.average, 0)+IF(y.personId = @personD, y.average, 0)+IF(z.personId = @personD, z.average, 0)+IF(e.personId = @personD, e.average, 0)+IF(f.personId = @personD, f.average, 0)+IF(g.personId = @personD, g.average, 0)+IF(h.personId = @personD, h.average, 0)+IF(i.personId = @personD, i.average, 0)+IF(j.personId = @personD, j.average, 0) 'personDTotal',
1264	(CASE WHEN @pAt > @pBt AND @pAt > @pCt AND @pAt > @pDt THEN @pAt WHEN @pBt > @pCt AND @pBt > @pDt THEN @pBt WHEN @pCt > @pDt THEN @pCt ELSE @pDt END) 'Total'
1265FROM
1266	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
1267CROSS JOIN
1268	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
1269CROSS JOIN
1270	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
1271CROSS JOIN
1272	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
1273CROSS JOIN
1274	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
1275CROSS JOIN
1276	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
1277CROSS JOIN
1278	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
1279CROSS JOIN
1280	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
1281CROSS JOIN
1282	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
1283CROSS JOIN
1284	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
1285CROSS JOIN
1286	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
1287CROSS JOIN
1288	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
1289CROSS JOIN
1290	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
1291ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC, personDTotal ASC;
1292
1293
1294
1295-- 4 Man Full Guildford, Auto Entry (result is automatically NULL if you don't have an average)
1296SET
1297@personA = '2016WHEA01',
1298@personB = '2017GOLD01',
1299@personC = '2015SPEN01',
1300@personD = '2015HAMM01';
1301--
1302
1303
1304SET
1305@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
1306@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
1307@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
1308@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
1309@666A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '666' AND format = 'a'),
1310@777A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '777' AND format = 'a'),
1311@333ftA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333ft' AND format = 'a'),
1312@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
1313@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
1314@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
1315@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
1316@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
1317@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
1318@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
1319@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
1320@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
1321@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
1322@666B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '666' AND format = 'a'),
1323@777B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '777' AND format = 'a'),
1324@333ftB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333ft' AND format = 'a'),
1325@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
1326@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
1327@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
1328@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
1329@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
1330@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a'),
1331@333C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333' AND format = 'a'),
1332@222C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '222' AND format = 'a'),
1333@444C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '444' AND format = 'a'),
1334@555C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '555' AND format = 'a'),
1335@666C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '666' AND format = 'a'),
1336@777C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '777' AND format = 'a'),
1337@333ftC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333ft' AND format = 'a'),
1338@333ohC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = '333oh' AND format = 'a'),
1339@clockC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'clock' AND format = 'a'),
1340@minxC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'minx' AND format = 'a'),
1341@pyramC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'pyram' AND format = 'a'),
1342@sq1C = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'sq1' AND format = 'a'),
1343@skewbC = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personC AND eventId = 'skewb' AND format = 'a'),
1344@333D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '333' AND format = 'a'),
1345@222D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '222' AND format = 'a'),
1346@444D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '444' AND format = 'a'),
1347@555D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '555' AND format = 'a'),
1348@666D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '666' AND format = 'a'),
1349@777D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '777' AND format = 'a'),
1350@333ftD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '333ft' AND format = 'a'),
1351@333ohD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = '333oh' AND format = 'a'),
1352@clockD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'clock' AND format = 'a'),
1353@minxD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'minx' AND format = 'a'),
1354@pyramD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'pyram' AND format = 'a'),
1355@sq1D = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'sq1' AND format = 'a'),
1356@skewbD = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personD AND eventId = 'skewb' AND format = 'a');
1357
1358DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
1359CREATE TABLE oj_stats.Optimal_Relay_Help
1360(personId TEXT, eventId TEXT, average INT);
1361INSERT INTO oj_stats.Optimal_Relay_Help
1362VALUES
1363	(@personA, '333', @333A),
1364	(@personA, '222', @222A),
1365	(@personA, '444', @444A),
1366	(@personA, '555', @555A),
1367	(@personA, '666', @666A),
1368	(@personA, '777', @777A),
1369	(@personA, '333ft', @333ftA),
1370	(@personA, '333oh', @333ohA),
1371	(@personA, 'clock', @clockA),
1372	(@personA, 'minx', @minxA),
1373	(@personA, 'pyram', @pyramA),
1374	(@personA, 'sq1', @sq1A),
1375	(@personA, 'skewb', @skewbA),
1376	(@personB, '333', @333B),
1377	(@personB, '222', @222B),
1378	(@personB, '444', @444B),
1379	(@personB, '555', @555B),
1380	(@personB, '666', @666B),
1381	(@personB, '777', @777B),
1382	(@personB, '333ft', @333ftB),
1383	(@personB, '333oh', @333ohB),
1384	(@personB, 'clock', @clockB),
1385	(@personB, 'minx', @minxB),
1386	(@personB, 'pyram', @pyramB),
1387	(@personB, 'sq1', @sq1B),
1388	(@personB, 'skewb', @skewbB),
1389	(@personC, '333', @333C),
1390	(@personC, '222', @222C),
1391	(@personC, '444', @444C),
1392	(@personC, '555', @555C),
1393	(@personC, '666', @666C),
1394	(@personC, '777', @777C),
1395	(@personC, '333ft', @333ftC),
1396	(@personC, '333oh', @333ohC),
1397	(@personC, 'clock', @clockC),
1398	(@personC, 'minx', @minxC),
1399	(@personC, 'pyram', @pyramC),
1400	(@personC, 'sq1', @sq1C),
1401	(@personC, 'skewb', @skewbC),
1402	(@personD, '333', @333D),
1403	(@personD, '222', @222D),
1404	(@personD, '444', @444D),
1405	(@personD, '555', @555D),
1406	(@personD, '666', @666D),
1407	(@personD, '777', @777D),
1408	(@personD, '333ft', @333ftD),
1409	(@personD, '333oh', @333ohD),
1410	(@personD, 'clock', @clockD),
1411	(@personD, 'minx', @minxD),
1412	(@personD, 'pyram', @pyramD),
1413	(@personD, 'sq1', @sq1D),
1414	(@personD, 'skewb', @skewbD);
1415
1416SET @pAt = 0, @pBt = 0, @pCt = 0, @pDt = 0;
1417DROP TABLE oj_stats.Optimal_Relay;
1418CREATE TABLE oj_stats.Optimal_Relay
1419SELECT a.personId '333', b.personId '222', c.personId '444', d.personId '555', x.personId '666', y.personId '777', z.personId '333ft', e.personId '333oh', f.personId 'clock', g.personId 'minx', h.personId 'pyram', i.personId 'skewb', j.personId 'sq1',
1420	@pAt := IF(a.personId = @personA, a.average, 0)+IF(b.personId = @personA, b.average, 0)+IF(c.personId = @personA, c.average, 0)+IF(d.personId = @personA, d.average, 0)+IF(x.personId = @personA, x.average, 0)+IF(y.personId = @personA, y.average, 0)+IF(z.personId = @personA, z.average, 0)+IF(e.personId = @personA, e.average, 0)+IF(f.personId = @personA, f.average, 0)+IF(g.personId = @personA, g.average, 0)+IF(h.personId = @personA, h.average, 0)+IF(i.personId = @personA, i.average, 0)+IF(j.personId = @personA, j.average, 0) 'personATotal',
1421	@pBt := IF(a.personId = @personB, a.average, 0)+IF(b.personId = @personB, b.average, 0)+IF(c.personId = @personB, c.average, 0)+IF(d.personId = @personB, d.average, 0)+IF(x.personId = @personB, x.average, 0)+IF(y.personId = @personB, y.average, 0)+IF(z.personId = @personB, z.average, 0)+IF(e.personId = @personB, e.average, 0)+IF(f.personId = @personB, f.average, 0)+IF(g.personId = @personB, g.average, 0)+IF(h.personId = @personB, h.average, 0)+IF(i.personId = @personB, i.average, 0)+IF(j.personId = @personB, j.average, 0) 'personBTotal',
1422	@pCt := IF(a.personId = @personC, a.average, 0)+IF(b.personId = @personC, b.average, 0)+IF(c.personId = @personC, c.average, 0)+IF(d.personId = @personC, d.average, 0)+IF(x.personId = @personC, x.average, 0)+IF(y.personId = @personC, y.average, 0)+IF(z.personId = @personC, z.average, 0)+IF(e.personId = @personC, e.average, 0)+IF(f.personId = @personC, f.average, 0)+IF(g.personId = @personC, g.average, 0)+IF(h.personId = @personC, h.average, 0)+IF(i.personId = @personC, i.average, 0)+IF(j.personId = @personC, j.average, 0) 'personCTotal',
1423	@pDt := IF(a.personId = @personD, a.average, 0)+IF(b.personId = @personD, b.average, 0)+IF(c.personId = @personD, c.average, 0)+IF(d.personId = @personD, d.average, 0)+IF(x.personId = @personD, x.average, 0)+IF(y.personId = @personD, y.average, 0)+IF(z.personId = @personD, z.average, 0)+IF(e.personId = @personD, e.average, 0)+IF(f.personId = @personD, f.average, 0)+IF(g.personId = @personD, g.average, 0)+IF(h.personId = @personD, h.average, 0)+IF(i.personId = @personD, i.average, 0)+IF(j.personId = @personD, j.average, 0) 'personDTotal',
1424	(CASE WHEN @pAt > @pBt AND @pAt > @pCt AND @pAt > @pDt THEN @pAt WHEN @pBt > @pCt AND @pBt > @pDt THEN @pBt WHEN @pCt > @pDt THEN @pCt ELSE @pDt END) 'Total'
1425FROM
1426	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
1427CROSS JOIN
1428	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
1429CROSS JOIN
1430	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
1431CROSS JOIN
1432	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
1433CROSS JOIN
1434	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '666' AND average IS NOT NULL) x
1435CROSS JOIN
1436	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '777' AND average IS NOT NULL) y
1437CROSS JOIN
1438	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333ft' AND average IS NOT NULL) z
1439CROSS JOIN
1440	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
1441CROSS JOIN
1442	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
1443CROSS JOIN
1444	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
1445CROSS JOIN
1446	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
1447CROSS JOIN
1448	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
1449CROSS JOIN
1450	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
1451ORDER BY Total ASC, personAtotal ASC, personBTotal ASC, personCTotal ASC, personDTotal ASC;