SQL ADD VALUES USING IFELSE AND RANGES

TABLE1
STUDENT TIME_STAMP  SCORE   TRIMESTERDATES
1   11/30/2021  4   NA
1   10/2/2021   3   NA
1   4/11/2021   4   NA
2   1/24/2021   2   NA
2   3/18/2021   2   NA
2   2/1/2020    8   NA
3   5/2/2021    10  NA
3   2/10/2021   10  NA
4   7/10/2020   1   NA
4   8/4/2020    3   NA
4   7/13/2020   2   NA
4   5/28/2020   4   NA
4   4/1/2020    4   NA
4   7/1/2021    5   NA
NA  NA  NA  2020-01-01
NA  NA  NA  2020-05-31
NA  NA  NA  2020-08-30
NA  NA  NA  2020-12-31
NA  NA  NA  2021-01-01
NA  NA  NA  2021-05-31
NA  NA  NA  2021-08-30
NA  NA  NA  2021-12-31

I wish to add up the SCORE values for each STUDENT across the TRIMESTERDATES ranges to get a table such as this

TABLE2
STUDENT SCORE   TIMES
1   …   2020-01-01--2020-05-31
1   …   2020-05-31--2020-08-30
1   …   2020-08-30--2020-12-31
1   …   2020-12-31--2021-01-01
1   …   2021-01-01--2021-05-31
1   …   2021-05-31--2021-08-30
1   …   2021-08-30--2021-12-31
2   …   2020-01-01--2020-05-31
2   …   2020-05-31--2020-08-30
2   …   2020-08-30--2020-12-31
2   …   2020-12-31--2021-01-01
2   …   2021-01-01--2021-05-31
2   …   2021-05-31--2021-08-30
2   …   2021-08-30--2021-12-31
3   …   2020-01-01--2020-05-31
3   …   2020-05-31--2020-08-30
3   …   2020-08-30--2020-12-31
3   …   2020-12-31--2021-01-01
3   …   2021-01-01--2021-05-31
3   …   2021-05-31--2021-08-30
3   …   2021-08-30--2021-12-31
4   …   2020-01-01--2020-05-31
4   …   2020-05-31--2020-08-30
4   …   2020-08-30--2020-12-31
4   …   2020-12-31--2021-01-01
4   …   2021-01-01--2021-05-31
4   …   2021-05-31--2021-08-30
4   …   2021-08-30--2021-12-31

>Solution :

Here’s the answer you wanted from the other question applied to this info.

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

Fiddle