From 158b36e51c8c2925d6c8af6e08380369d019378e Mon Sep 17 00:00:00 2001
From: TinWoodman92 <chrhodgden@gmail.com>
Date: Sat, 16 Dec 2023 06:18:44 -0600
Subject: Initial commit

---
 wdl_stats.sql | 59 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 59 insertions(+)
 create mode 100644 wdl_stats.sql

(limited to 'wdl_stats.sql')

diff --git a/wdl_stats.sql b/wdl_stats.sql
new file mode 100644
index 0000000..d247ee0
--- /dev/null
+++ b/wdl_stats.sql
@@ -0,0 +1,59 @@
+SELECT 
+	:plr_1_name AS plr_1_name,
+	:plr_1_text_color AS plr_1_text_color,
+	:plr_2_name AS plr_2_name,
+	:plr_2_text_color AS plr_2_text_color,
+	sum(CASE 
+		WHEN plr_X_name = :plr_1_name 
+		AND plr_X_color_int = :plr_1_color_int
+		AND victor_name = :plr_1_name 
+		AND victor_color_int = :plr_1_color_int 
+		THEN 1 ELSE 0 END) AS plr_1_X_wins,
+	sum(CASE 
+		WHEN plr_O_name = :plr_1_name 
+		AND plr_O_color_int = :plr_1_color_int
+		AND victor_name = :plr_1_name 
+		AND victor_color_int = :plr_1_color_int 
+		THEN 1 ELSE 0 END) AS plr_1_O_wins,
+	sum(CASE 
+		WHEN plr_X_name = :plr_2_name 
+		AND plr_X_color_int = :plr_2_color_int
+		AND victor_name = :plr_2_name 
+		AND victor_color_int = :plr_2_color_int 
+		THEN 1 ELSE 0 END) AS plr_2_X_wins,
+	sum(CASE 
+		WHEN plr_O_name = :plr_2_name 
+		AND plr_O_color_int = :plr_2_color_int
+		AND victor_name = :plr_2_name 
+		AND victor_color_int = :plr_2_color_int 
+		THEN 1 ELSE 0 END) AS plr_2_O_wins,
+	sum(CASE 
+		WHEN plr_X_name = :plr_1_name 
+		AND plr_X_color_int = :plr_1_color_int
+		AND plr_O_name = :plr_2_name 
+		AND plr_O_color_int = :plr_2_color_int
+		AND victor_name = '' 
+		AND victor_color_int = 0
+		THEN 1 ELSE 0 END) AS game_X_O_draws,
+	sum(CASE 
+		WHEN plr_X_name = :plr_2_name 
+		AND plr_X_color_int = :plr_2_color_int
+		AND plr_O_name = :plr_1_name 
+		AND plr_O_color_int = :plr_1_color_int
+		AND victor_name = '' 
+		AND victor_color_int = 0
+		THEN 1 ELSE 0 END) AS game_O_X_draws
+FROM game_log
+WHERE
+	(
+		plr_X_name = :plr_1_name 
+		AND plr_X_color_int = :plr_1_color_int
+		AND plr_O_name = :plr_2_name 
+		AND plr_O_color_int = :plr_2_color_int
+	) OR (
+		plr_X_name = :plr_2_name 
+		AND plr_X_color_int = :plr_2_color_int
+		AND plr_O_name = :plr_1_name 
+		AND plr_O_color_int = :plr_1_color_int
+	)
+;
\ No newline at end of file
-- 
cgit v1.2.3

