Share via

Always get latest score that is not 0 or null

Rontech10111 201 Reputation points
2025-10-03T12:32:00.38+00:00

Hi,

I have a table like the below with scores.

CREATE TABLE [dbo].[Student_Scores](

[Month] [int] NULL,

[Student_no] [bigint] NULL,

[Score] [numeric](18, 2) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[Student_Scores] ([Month], [Student_no], [Score]) VALUES (202507, 87, CAST(0.00 AS Numeric(18, 2)))

INSERT [dbo].[Student_Scores] ([Month], [Student_no], [Score]) VALUES (202505, 87, CAST(97.60 AS Numeric(18, 2)))

INSERT [dbo].[Student_Scores] ([Month], [Student_no], [Score]) VALUES (202506, 87, NULL)

INSERT [dbo].[Student_Scores] ([Month], [Student_no], [Score]) VALUES (202508, 87, CAST(89.20 AS Numeric(18, 2)))

INSERT [dbo].[Student_Scores] ([Month], [Student_no], [Score]) VALUES (202509, 87, CAST(0.00 AS Numeric(18, 2)))

GO

For any new month where the score is a null or a 0, that month should take the score of the last period that had a score.

So for example,

  • the final score for the student for 202505 will be 97.6.
  • the final score for 250506 will be 97.6 (because 202506 is null, we take the latest score that is not null or 0)
  • the final score for 250507 will be 97.6 (because 202507 is a 0, we take the latest score that is not null or 0)
  • the final score for 250508 will be 89.2 (because 202508, we take the score that is not null or 0
  • the final score for 250509 will be 89.2 (because 202509 is a 0, we take the latest score that is not null or 0)
  • and so on...

Kindly advise

Thanks

SQL Server | SQL Server Transact-SQL
Answer accepted by question author
  1. Viorel 124.9K Reputation points
    2025-10-03T12:49:01.24+00:00

    Did you try something? I only tried this:

    select [Month], Student_no, 
	(select top(1) Score from Student_Scores where Student_no = s.Student_no and [Month] <= s.[Month] and Score is not null and Score > 0 order by [Month] desc) as Score
from Student_Scores s
order by [Month]

    A CASE can be added too.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 27,011 Reputation points
    2025-10-05T00:01:17.7166667+00:00

    Hi @Rontech10111,

    Here is a more efficient way to implement it in SQL Server 2022 onwards.

    It is leveraging LAST_VALUE() function which respects NULL values.

    One single SELECT, no joins, no correlated subquery.

    -- SQL Server 2022 onwards
SELECT [Month],
    Student_no,
    Score,
    CASE 
        WHEN Score IS NULL OR Score = 0 THEN
            LAST_VALUE(CASE WHEN Score IS NOT NULL AND Score <> 0 THEN Score END) 
                IGNORE NULLS OVER (
                    PARTITION BY Student_no 
                    ORDER BY [Month]
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                )
        ELSE Score
    END AS Adjusted_Score
FROM dbo.Student_Scores
ORDER BY Student_no, [Month];
    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.