Recursive Function Compare Values in MS SQL Server

I have a table that has three columns. One is the MemberID, the other column is the score that they got, and the other one is the date when they got the score.

I want to create a result set that is going to show me a list of all the members that improved their scores overtime.

So I think that for this, a recursive table would be the answer. But I don’t know how to create it.

Here is a list of my columns and some values:

MemberID    Score    Dates
1           3        12/31/2019
1           2        01/31/2020
1           1        02/31/2020
2           4        12/31/2019
2           6        01/31/2020
2           10       02/31/2020
3           4        12/31/2019
3           6        01/31/2020
3           5        02/31/2020

In this instance, my result set would only include:

MemberID 2, because it is the only member that improved over time.

Answer

You can use analytic functions rather than recursion to solve this.

If you’re looking for whether the member’s latest score is higher than their earliest score, something like the following should work:

Select Distinct MemberID From (
  Select MemberID,
  First_Value(Score) Over (Partition by MemberID Order By Dates) First_Score,
  Last_Value(Score) Over (Partition by MemberID Order by Dates) Last_Score
From Scores
) Where Last_Score > First_Score;