IF object_id('tmp_players', 'U') is not null DROP TABLE tmp_players;
IF object_id('tmp_events', 'U') is not null DROP TABLE tmp_events;
IF object_id('events', 'U') is not null DROP TABLE events;
IF object_id('players', 'U') is not null DROP TABLE players;
GO

CREATE TABLE tmp_players (
	player_teamId int,
	player_shirtNr int,
	player_name nvarchar(100)
);

CREATE TABLE tmp_events (
	evt_goalA int,
	evt_goalB int,
	evt_when datetime2,
	evt_cat int,
	evt_player int
);

INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 14,  'Rea Reka Meszaros' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 15,  'Kinga Klivinyi' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 16,  'Blanka Biro' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 19,  'Kata Farkas' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 21,  'Greta Marton' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 22,  'Viktoria Lukacs' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 24,  'Danick Snelder' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 25,  'Nerea Pena Abaurrea' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 27,  'Luca Hafra' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 31,  'Zsofia Szemerey' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 45,  'Noemi Hafra' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 48,  'Dorottya Faluvegi' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 6,  'Nadine Schatzl' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 91,  'Aniko Kovacsics' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 20, 92,  'Dora Hornyak' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 10,  'Vilde Kaurin Jonassen' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 11,  'Silje Katrine Waade' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 12,  'Eline Fagerheim' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 15,  'Linn Jorum Sulland' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 16,  'Katrine Lunde' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 2,  'Karoline Olsen' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 20,  'Jeanett Kristiansen' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 22,  'Marta Tomac' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 24,  'Hanna Maria Yttereng' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 25,  'Henny Ella Reistad' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 27,  'Sunniva Amalie Naes Andersen' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 3,  'Emilie Hegh Arntzen' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 4,  'Tonje Refsnes' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 6,  'Malin Aune' );
INSERT INTO tmp_players (player_teamId, player_shirtNr, player_name) VALUES ( 40, 9,  'Mathilde Kristensen' );

INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 27, 26, '2018.10.14 16:04:33', 86, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 27, 26, '2018.10.14 16:04:33', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 27, 26, '2018.10.14 16:04:40', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:03:30', 11, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:03:30', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:02:28', 10, 20 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:02:26', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:02:13', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:01:48', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 26, '2018.10.14 16:01:18', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 25, '2018.10.14 16:01:05', 11, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 25, '2018.10.14 16:01:05', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 25, '2018.10.14 15:59:55', 10, 40 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 25, '2018.10.14 15:59:53', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 26, 25, '2018.10.14 16:00:08', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 25, '2018.10.14 15:59:13', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 25, '2018.10.14 15:57:39', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 25, '2018.10.14 15:57:20', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 24, '2018.10.14 15:56:53', 91, 2092 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 24, '2018.10.14 15:56:26', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 24, '2018.10.14 15:55:52', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 24, '2018.10.14 15:55:50', 5, 406 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 25, 24, '2018.10.14 15:55:29', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:55:28', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:54:54', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:54:40', 11, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:54:40', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:53:30', 10, 40 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:53:28', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 24, 24, '2018.10.14 15:53:07', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 24, '2018.10.14 15:53:04', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 24, '2018.10.14 15:52:51', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 24, '2018.10.14 15:52:38', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 24, '2018.10.14 15:51:22', 91, 409 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 23, '2018.10.14 15:50:26', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 23, '2018.10.14 15:49:33', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:49:26', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:49:17', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:48:53', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:48:24', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:48:11', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:48:01', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:47:59', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:46:50', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 22, '2018.10.14 15:47:01', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 21, '2018.10.14 15:46:40', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 20, '2018.10.14 15:45:55', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 20, '2018.10.14 15:45:22', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 23, 19, '2018.10.14 15:43:29', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:43:24', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:43:05', 5, 4022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:42:57', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:41:44', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:41:34', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:41:22', 5, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:41:15', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:40:12', 91, 2021 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 19, '2018.10.14 15:40:10', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 18, '2018.10.14 15:38:55', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 18, '2018.10.14 15:38:43', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 18, '2018.10.14 15:38:36', 5, 4011 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 18, '2018.10.14 15:36:57', 91, 4022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 22, 18, '2018.10.14 15:36:44', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 21, 18, '2018.10.14 15:35:55', 91, 409 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 21, 17, '2018.10.14 15:35:16', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 20, 17, '2018.10.14 15:35:12', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 20, 17, '2018.10.14 15:34:54', 5, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 20, 17, '2018.10.14 15:34:44', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 20, 17, '2018.10.14 15:34:09', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 20, 17, '2018.10.14 15:34:13', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 19, 17, '2018.10.14 15:33:32', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 19, 16, '2018.10.14 15:33:20', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 19, 16, '2018.10.14 15:33:12', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 19, 16, '2018.10.14 15:32:48', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 16, '2018.10.14 15:32:43', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 16, '2018.10.14 15:32:22', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 16, '2018.10.14 15:32:12', 91, 4010 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 15, '2018.10.14 15:31:50', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 15, '2018.10.14 15:30:53', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 18, 14, '2018.10.14 15:29:49', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:29:28', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:28:59', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:28:36', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:28:01', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:26:58', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:26:17', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:25:31', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:24:21', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:24:21', 73, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:09:14', 72, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:09:14', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 14, '2018.10.14 15:09:00', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:08:08', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:07:40', 11, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:07:40', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:05:24', 10, 20 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:05:23', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 13, '2018.10.14 15:05:06', 91, 406 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 12, '2018.10.14 15:04:08', 91, 4010 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 11, '2018.10.14 15:03:27', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 10, '2018.10.14 15:03:19', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 10, '2018.10.14 15:02:59', 5, 2048 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 10, '2018.10.14 15:02:52', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 17, 10, '2018.10.14 15:02:36', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 16, 10, '2018.10.14 15:02:12', 91, 403 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 16, 10, '2018.10.14 15:01:40', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 15, 10, '2018.10.14 15:01:10', 91, 4010 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 15, 10, '2018.10.14 15:00:40', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 10, '2018.10.14 15:00:22', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 10, '2018.10.14 15:00:06', 4, 40 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 10, '2018.10.14 14:59:56', 5, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 10, '2018.10.14 14:59:46', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 10, '2018.10.14 14:59:18', 91, 4025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 9, '2018.10.14 14:58:11', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 14, 8, '2018.10.14 14:57:58', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 8, '2018.10.14 14:57:23', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 8, '2018.10.14 14:57:18', 5, 2022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 8, '2018.10.14 14:57:04', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 8, '2018.10.14 14:57:17', 91, 4010 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 7, '2018.10.14 14:56:58', 91, 403 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:56:29', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:56:26', 4, 2014 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:56:17', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:55:39', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:55:22', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 13, 6, '2018.10.14 14:55:07', 91, 2014 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:54:24', 91, 2015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:53:51', 91, 4024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:53:09', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:52:22', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:51:13', 4, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:50:37', 11, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:50:37', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:49:18', 10, 40 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:49:16', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 12, 6, '2018.10.14 14:49:40', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 11, 6, '2018.10.14 14:48:29', 91, 406 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 11, 6, '2018.10.14 14:48:01', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 10, 6, '2018.10.14 14:47:26', 91, 403 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 10, 6, '2018.10.14 14:46:50', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 9, 6, '2018.10.14 14:46:21', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 9, 6, '2018.10.14 14:46:07', 5, 4022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 9, 6, '2018.10.14 14:45:55', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 9, 6, '2018.10.14 14:45:17', 91, 403 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 9, 6, '2018.10.14 14:44:33', 91, 2022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 8, 6, '2018.10.14 14:44:21', 91, 406 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 8, 5, '2018.10.14 14:43:35', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 5, '2018.10.14 14:43:29', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 5, '2018.10.14 14:43:10', 5, 409 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 5, '2018.10.14 14:42:42', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 5, '2018.10.14 14:42:08', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 5, '2018.10.14 14:42:07', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 7, 4, '2018.10.14 14:41:43', 91, 206 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:40:59', 91, 4022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:40:34', 91, 2092 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:40:17', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:40:02', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:39:54', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 6, 4, '2018.10.14 14:39:42', 91, 206 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 5, 4, '2018.10.14 14:39:23', 91, 409 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 5, 3, '2018.10.14 14:39:00', 91, 2045 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 4, 3, '2018.10.14 14:38:20', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:37:59', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:37:55', 4, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:37:51', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:37:18', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:37:17', 5, 4024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:36:36', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:36:25', 91, 4020 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:35:57', 91, 2022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 3, '2018.10.14 14:35:56', 91, 4022 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 2, '2018.10.14 14:35:11', 4, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 3, 2, '2018.10.14 14:34:17', 91, 2025 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 2, '2018.10.14 14:33:46', 91, 406 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 1, '2018.10.14 14:32:51', 4, 4024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 1, '2018.10.14 14:32:45', 91, 4015 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 0, '2018.10.14 14:32:10', 84, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 0, '2018.10.14 14:31:26', 85, 0 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 2, 0, '2018.10.14 14:31:45', 91, 2091 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 1, 0, '2018.10.14 14:30:50', 91, 2024 );
INSERT INTO tmp_events (evt_goalA, evt_goalB, evt_when, evt_cat, evt_player) VALUES ( 0, 0, '2018.10.14 14:30:00', 91, 2024 );

-- SELECT * FROM tmp_players;
-- SELECT * FROM tmp_events;

-- 1+2.

CREATE TABLE players (
	player_id int primary key,
	player_name nvarchar(100)
);
INSERT INTO players
	SELECT concat(player_teamId, player_shirtNr), player_name
	FROM tmp_players;
-- SELECT * FROM players;
CREATE TABLE events (
	evt_id int identity primary key,
	evt_goalA int,
	evt_goalB int,
	evt_when datetime2,
	evt_cat int,
	evt_player int references players(player_id)
);
INSERT INTO events
	SELECT tmp_events.*
	FROM tmp_events INNER JOIN players ON (player_id = evt_player)
	-- WHERE evt_player IN (select player_id from players);
	ORDER BY evt_when asc;
-- SELECT * FROM events;

-- 3.
SELECT *
FROM events INNER JOIN players ON (evt_player = player_id);

-- 4.
SELECT min(evt_when) as minEvt, max(evt_when) as maxEvt,
	datediff(minute, min(evt_when), max(evt_when))/60.0 as diff
FROM events;

-- 5.
ALTER TABLE events ADD evt_cattext nvarchar(50);
GO
UPDATE events SET evt_cattext = CASE evt_cat
	WHEN 4 THEN 'YELLOWCARD'
	WHEN 5 THEN 'SUSP_2MINS'
	WHEN 91 THEN 'SHOOTS'
END; 
SELECT * FROM events;

-- 6.
-- self join
SELECT evtCurrent.evt_id
FROM events evtCurrent LEFT JOIN events evtBefore ON (evtCurrent.evt_id = evtBefore.evt_id + 1)
WHERE	evtCurrent.evt_goalA<>isnull(evtBefore.evt_goalA, 0) OR 
		evtCurrent.evt_goalB<>isnull(evtBefore.evt_goalB, 0);

IF OBJECT_ID('goals', 'V') is not null DROP VIEW goals;
GO
CREATE VIEW goals AS
	WITH subQuery AS (
		SELECT evt_id, evt_goalA, evt_goalB,
			LAG(evt_goalA, 1, 0) OVER (ORDER BY evt_id ASC) as prevGoalA,
			LAG(evt_goalB, 1, 0) OVER (ORDER BY evt_id ASC) as prevGoalB
		FROM events
	)
	SELECT evt_id 
	FROM subQuery
	WHERE evt_goalA<>prevGoalA OR evt_goalB<>prevGoalB;
GO
SELECT * FROM goals;

UPDATE events SET evt_cattext = 'SCORES' FROM events INNER JOIN goals ON (events.evt_id = goals.evt_id);
-- SELECT * FROM events;

-- 7.
SELECT LEFT(evt_player, 2) as teamId, evt_cattext, count(1) as numEvents
FROM events
GROUP BY evt_cattext, LEFT(evt_player, 2)
ORDER BY evt_cattext;

SELECT * from events WHERE LEFT(evt_player,2)='20' and evt_cattext='SCORES';
SELECT * from events WHERE LEFT(evt_player,2)='40' and evt_cattext='SCORES';
-- BAD source data!!!
/*
DECLARE @sql nvarchar(200);
SET @sql = 'SELECT * FROM emp;SELECT * from dept';
EXEC sp_executesql @sql;
*/