Supplemental Applications Received report (final)2

WITH RankedScores AS (
    SELECT supp.[Id],
           supp.[ApplicantId],
           supp.[Year],
           supp.[IsPaid],
           supp.[IsSubmitted],
		   appl.[TmdsasUserId],
		   us.[UserID],
           us.FirstName,
           us.LastName,
           score.DateModified,
           ROW_NUMBER() OVER (PARTITION BY  supp.Id ORDER BY score.DateModified DESC) AS RowNum
    FROM [ProfessionalProgram].[dbo].[Ads_SupplementalApplication] supp
    LEFT JOIN [ProfessionalProgram].[dbo].[Ads_Applicant] appl ON appl.Id = supp.ApplicantId
    LEFT JOIN Users.dbo.tblUser us ON us.UserID = appl.UserId
    LEFT JOIN [ProfessionalProgram].[dbo].[Ads_SupplementalApplicationScore] score
        ON score.SupplementalApplicationId = supp.Id 
    WHERE supp.Year = 2025
)
SELECT RankedScores.[Id],
       RankedScores.[ApplicantId],
       RankedScores.[Year],
       RankedScores.[IsPaid],
       RankedScores.[IsSubmitted],
	   RankedScores.[TmdsasUserId],
	   RankedScores.[UserID],
       RankedScores.FirstName,
       RankedScores.LastName,
       RankedScores.DateModified AS LastSubmittedScoreDate
FROM RankedScores
WHERE RankedScores.RowNum = 1
  AND RankedScores.DateModified IS NOT NULL
  AND RankedScores.DateModified BETWEEN '2024-08-30' AND GETDATE()
ORDER BY LastSubmittedScoreDate DESC;