SQL Server 2019

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

CREATE TABLE RatesbyDay(
    RateDate DATE,
    RateMiles INT);
INSERT INTO RatesbyDay VALUES ('2020-01-01',600)
INSERT INTO RatesbyDay VALUES ('2020-01-02',450)
INSERT INTO RatesbyDay VALUES ('2020-01-03',370)
INSERT INTO RatesbyDay VALUES ('2020-01-04',700)
INSERT INTO RatesbyDay VALUES ('2020-01-05',100)
INSERT INTO RatesbyDay VALUES ('2020-01-06',480)
INSERT INTO RatesbyDay VALUES ('2020-01-07',430)
INSERT INTO RatesbyDay VALUES ('2020-01-08',200)
INSERT INTO RatesbyDay VALUES ('2020-01-09',590)
INSERT INTO RatesbyDay VALUES ('2020-01-10',380)
INSERT INTO RatesbyDay VALUES ('2020-01-11',220)
INSERT INTO RatesbyDay VALUES ('2020-01-12',320)
INSERT INTO RatesbyDay VALUES ('2020-01-13',360)
INSERT INTO RatesbyDay VALUES ('2020-01-14',600)
INSERT INTO RatesbyDay VALUES ('2020-01-15',450)
INSERT INTO RatesbyDay VALUES ('2020-01-16',475)
INSERT INTO RatesbyDay VALUES ('2020-01-17',300)
INSERT INTO RatesbyDay VALUES ('2020-01-18',190)
INSERT INTO RatesbyDay VALUES ('2020-01-19',435)
INSERT INTO RatesbyDay VALUES ('2020-01-20',285)
INSERT INTO RatesbyDay VALUES ('2020-01-21',350)
INSERT INTO RatesbyDay VALUES ('2020-01-22',410)
INSERT INTO RatesbyDay VALUES ('2020-01-23',250)
INSERT INTO RatesbyDay VALUES ('2020-01-24',300)
INSERT INTO RatesbyDay VALUES ('2020-01-25',250)
INSERT INTO RatesbyDay VALUES ('2020-01-26',650)
INSERT INTO RatesbyDay VALUES ('2020-01-27',180)
INSERT INTO RatesbyDay VALUES ('2020-01-28',280)
INSERT INTO RatesbyDay VALUES ('2020-01-29',200)
INSERT INTO RatesbyDay VALUES ('2020-01-30',100)
INSERT INTO RatesbyDay VALUES ('2020-01-31',100)
31 rows affected

select r.*, sum(ratemiles) over(order by ratedate) sm from ratesbyday r


RateDate
RateMiles
sm


2020-01-01
600
600

2020-01-02
450
1050

2020-01-03
370
1420

2020-01-04
700
2120

2020-01-05
100
2220

2020-01-06
480
2700

2020-01-07
430
3130

2020-01-08
200
3330

2020-01-09
590
3920

2020-01-10
380
4300

2020-01-11
220
4520

2020-01-12
320
4840

2020-01-13
360
5200

2020-01-14
600
5800

2020-01-15
450
6250

2020-01-16
475
6725

2020-01-17
300
7025

2020-01-18
190
7215

2020-01-19
435
7650

2020-01-20
285
7935

2020-01-21
350
8285

2020-01-22
410
8695

2020-01-23
250
8945

2020-01-24
300
9245

2020-01-25
250
9495

2020-01-26
650
10145

2020-01-27
180
10325

2020-01-28
280
10605

2020-01-29
200
10805

2020-01-30
100
10905

2020-01-31
100
11005


with 
    data as (select r.*, row_number() over(order by ratedate) rn from ratesbyday r),
    cte as (
        select d.*, ratemiles total, ratemiles newtotal from data d where rn = 1
        union all
        select d.*, 
            c.newtotal + d.ratemiles,
            case when c.newtotal < 3000 and c.newtotal + d.ratemiles >= 3000 then 0 else c.newtotal + d.ratemiles end
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select ratedate, ratemiles, total 
from cte 
where newtotal = 0
order by ratedate


ratedate
ratemiles
total


2020-01-07
430
3130

2020-01-15
450
3120

2020-01-25
250
3245

RateDate	RateMiles	sm
2020-01-01	600	600
2020-01-02	450	1050
2020-01-03	370	1420
2020-01-04	700	2120
2020-01-05	100	2220
2020-01-06	480	2700
2020-01-07	430	3130
2020-01-08	200	3330
2020-01-09	590	3920
2020-01-10	380	4300
2020-01-11	220	4520
2020-01-12	320	4840
2020-01-13	360	5200
2020-01-14	600	5800
2020-01-15	450	6250
2020-01-16	475	6725
2020-01-17	300	7025
2020-01-18	190	7215
2020-01-19	435	7650
2020-01-20	285	7935
2020-01-21	350	8285
2020-01-22	410	8695
2020-01-23	250	8945
2020-01-24	300	9245
2020-01-25	250	9495
2020-01-26	650	10145
2020-01-27	180	10325
2020-01-28	280	10605
2020-01-29	200	10805
2020-01-30	100	10905
2020-01-31	100	11005