SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
    (STUDENT int, TIME_STAMP date, SCORE int, TRIMESTERDATES date)
;
    
INSERT INTO t
    (STUDENT, TIME_STAMP, SCORE, TRIMESTERDATES)
VALUES
    ('1', '11/30/2021', '4', NULL),
    ('1', '10/2/2021', '3', NULL),
    ('1', '4/11/2021', '4', NULL),
    ('2', '1/24/2021', '2', NULL),
    ('2', '3/18/2021', '2', NULL),
    ('2', '2/1/2020', '8', NULL),
    ('3', '5/2/2021', '10', NULL),
    ('3', '2/10/2021', '10', NULL),
    ('4', '7/10/2020', '1', NULL),
    ('4', '8/4/2020', '3', NULL),
    ('4', '7/13/2020', '2', NULL),
    ('4', '5/28/2020', '4', NULL),
    ('4', '4/1/2020', '4', NULL),
    ('4', '7/1/2021', '5', NULL),
    (NULL, NULL, NULL, '2020-01-01'),
    (NULL, NULL, NULL, '2020-05-31'),
    (NULL, NULL, NULL, '2020-08-30'),
    (NULL, NULL, NULL, '2020-12-31'),
    (NULL, NULL, NULL, '2021-01-01'),
    (NULL, NULL, NULL, '2021-05-31'),
    (NULL, NULL, NULL, '2021-08-30'),
    (NULL, NULL, NULL, '2021-12-31')
;
22 rows affected

select * from t


STUDENT
TIME_STAMP
SCORE
TRIMESTERDATES


1
2021-11-30
4
null

1
2021-10-02
3
null

1
2021-04-11
4
null

2
2021-01-24
2
null

2
2021-03-18
2
null

2
2020-02-01
8
null

3
2021-05-02
10
null

3
2021-02-10
10
null

4
2020-07-10
1
null

4
2020-08-04
3
null

4
2020-07-13
2
null

4
2020-05-28
4
null

4
2020-04-01
4
null

4
2021-07-01
5
null

null
null
null
2020-01-01

null
null
null
2020-05-31

null
null
null
2020-08-30

null
null
null
2020-12-31

null
null
null
2021-01-01

null
null
null
2021-05-31

null
null
null
2021-08-30

null
null
null
2021-12-31


with t2 as (
            select TRIMESTERDATES                                                                                   as semester_start
                  ,coalesce(lead(TRIMESTERDATES) over(order by TRIMESTERDATES), dateadd(year, 1, TRIMESTERDATES))   as semester_end
                  ,row_number() over(order by TRIMESTERDATES)                                                       as semester_id  
            from   t
            where  TRIMESTERDATES is not null
           )
select      student
           ,sum(score)  as total_score
           ,semester_start
           ,semester_end
from        t join t2 on t.time_stamp between semester_start and semester_end
group by    student, semester_id, semester_start, semester_end
order by    1


student
total_score
semester_start
semester_end


1
4
2021-01-01
2021-05-31

1
7
2021-08-30
2021-12-31

2
8
2020-01-01
2020-05-31

2
4
2021-01-01
2021-05-31

3
20
2021-01-01
2021-05-31

4
8
2020-01-01
2020-05-31

4
6
2020-05-31
2020-08-30

4
5
2021-05-31
2021-08-30

STUDENT	TIME_STAMP	SCORE	TRIMESTERDATES
1	2021-11-30	4	null
1	2021-10-02	3	null
1	2021-04-11	4	null
2	2021-01-24	2	null
2	2021-03-18	2	null
2	2020-02-01	8	null
3	2021-05-02	10	null
3	2021-02-10	10	null
4	2020-07-10	1	null
4	2020-08-04	3	null
4	2020-07-13	2	null
4	2020-05-28	4	null
4	2020-04-01	4	null
4	2021-07-01	5	null
null	null	null	2020-01-01
null	null	null	2020-05-31
null	null	null	2020-08-30
null	null	null	2020-12-31
null	null	null	2021-01-01
null	null	null	2021-05-31
null	null	null	2021-08-30
null	null	null	2021-12-31