Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Short SQL Stumper

Author  Topic  Tim Starting Member 392 Posts Posted - 2000-11-19 : 21:28:54 Hi folks, I asked the SQL team this one, but I wonder if any other gurus out there can do it? It sure beats the hell out of me! I am making a database to record results of matches played at my tennis club. Simple 3-table database design is this: Player - Player_ID - Name Match - Match_ID - Date Result - Result_ID - Match_ID - Player_ID - Won - Lost - Draw Won Lost and Draw columns can have values of 1 or 0 and there would be two result records for each match played. It is pretty easy to generate a list of players and total matches won, lost and drawn. I am stuck on the 'streak' query which shows each player and their current streak as follows: if player won last 4 consecutive matches then streak = 4 if player lost last 2 consecutive matches then streak = -2 if player drew last match played then streak = 0 Any ideas? a tweak to the design for this or just some clever SQL ?