USQHpdXC

1SET
2@personA = '2016WHEA01',
3@personB = '2017GOLD02';
4--
5
6
7SET
8@333A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333' AND format = 'a'),
9@222A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '222' AND format = 'a'),
10@444A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '444' AND format = 'a'),
11@555A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '555' AND format = 'a'),
12@333ohA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = '333oh' AND format = 'a'),
13@clockA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'clock' AND format = 'a'),
14@minxA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'minx' AND format = 'a'),
15@pyramA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'pyram' AND format = 'a'),
16@sq1A = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'sq1' AND format = 'a'),
17@skewbA = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personA AND eventId = 'skewb' AND format = 'a'),
18@333B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333' AND format = 'a'),
19@222B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '222' AND format = 'a'),
20@444B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '444' AND format = 'a'),
21@555B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '555' AND format = 'a'),
22@333ohB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = '333oh' AND format = 'a'),
23@clockB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'clock' AND format = 'a'),
24@minxB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'minx' AND format = 'a'),
25@pyramB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'pyram' AND format = 'a'),
26@sq1B = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'sq1' AND format = 'a'),
27@skewbB = (SELECT result FROM wca_stats.Ranks_All WHERE personId = @personB AND eventId = 'skewb' AND format = 'a');
28
29DROP TABLE IF EXISTS oj_stats.Optimal_Relay_Help;
30CREATE TABLE oj_stats.Optimal_Relay_Help
31(personId TEXT, eventId TEXT, average INT);
32INSERT INTO oj_stats.Optimal_Relay_Help
33VALUES
34	(@personA, '333', @333A),
35	(@personA, '222', @222A),
36	(@personA, '444', @444A),
37	(@personA, '555', @555A),
38	(@personA, '333oh', @333ohA),
39	(@personA, 'clock', @clockA),
40	(@personA, 'minx', @minxA),
41	(@personA, 'pyram', @pyramA),
42	(@personA, 'sq1', @sq1A),
43	(@personA, 'skewb', @skewbA),
44	(@personB, '333', @333B),
45	(@personB, '222', @222B),
46	(@personB, '444', @444B),
47	(@personB, '555', @555B),
48	(@personB, '333oh', @333ohB),
49	(@personB, 'clock', @clockB),
50	(@personB, 'minx', @minxB),
51	(@personB, 'pyram', @pyramB),
52	(@personB, 'sq1', @sq1B),
53	(@personB, 'skewb', @skewbB);
54
55SET @pAt = 0, @pBt = 0;
56DROP TABLE oj_stats.Optimal_Relay;
57CREATE TABLE oj_stats.Optimal_Relay
58SELECT 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',
59	@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',
60	@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',
61	(CASE WHEN @pAt > @pBt THEN @pAt ELSE @pBt END) 'Total'
62FROM
63	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333' AND average IS NOT NULL) a
64CROSS JOIN
65	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '222' AND average IS NOT NULL) b
66CROSS JOIN
67	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '444' AND average IS NOT NULL) c
68CROSS JOIN
69	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '555' AND average IS NOT NULL) d
70CROSS JOIN
71	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = '333oh' AND average IS NOT NULL) e
72CROSS JOIN
73	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'clock' AND average IS NOT NULL) f
74CROSS JOIN
75	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'minx' AND average IS NOT NULL) g
76CROSS JOIN
77	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'pyram' AND average IS NOT NULL) h
78CROSS JOIN
79	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'skewb' AND average IS NOT NULL) i
80CROSS JOIN
81	(SELECT * FROM oj_stats.Optimal_Relay_Help WHERE eventId = 'sq1' AND average IS NOT NULL) j
82ORDER BY Total ASC, personAtotal ASC, personBTotal ASC;