SQL Server 2017

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

CREATE TABLE sendMail (
	date date NOT NULL,
	profit int NULL,
	ReviseP int NULL,
	flag nchar (1) NULL
) 


INSERT  sendMail  
VALUES (CAST(N'2019-10-01' AS Date), -3200, -3200, N'0')
,  (CAST(N'2019-10-02' AS Date), -3600, -3600, N'0')
,  (CAST(N'2019-10-03' AS Date), -3000, -3000, N'0')
,  (CAST(N'2019-10-04' AS Date), -4200, -4200, N'0')
,  (CAST(N'2019-10-07' AS Date), 2000, 2000, N'0')
,  (CAST(N'2019-10-08' AS Date), 1000, 1000, N'0')
,  (CAST(N'2019-10-09' AS Date), 1200, 1200, N'0')
,  (CAST(N'2019-10-10' AS Date), -2600, -2600, N'0')
,  (CAST(N'2019-10-11' AS Date), -1800, -1800, N'0')
,  (CAST(N'2019-10-14' AS Date), -3600, -3600, N'0')
,  (CAST(N'2019-10-15' AS Date), -8200, -8200, N'0')
,  (CAST(N'2019-10-16' AS Date), -6200, -6200, N'0')
,  (CAST(N'2019-10-17' AS Date), -5000, -5000, N'0')
,  (CAST(N'2019-10-18' AS Date), -3600, -3600, N'0')
,  (CAST(N'2019-10-21' AS Date), 2200, 2200, N'0')
,  (CAST(N'2019-10-22' AS Date), -3000, -3000, N'0')
,  (CAST(N'2019-10-23' AS Date), -7100, -7100, N'0')
,  (CAST(N'2019-10-24' AS Date), -8200, -8200, N'0')
,  (CAST(N'2019-10-25' AS Date), -9600, -9600, N'0')
,  (CAST(N'2019-10-28' AS Date), -12000, -12000, N'0')
,  (CAST(N'2019-10-29' AS Date), -11000, -11000, N'0')
,  (CAST(N'2019-10-30' AS Date), -8000, -8000, N'0')
,  (CAST(N'2019-10-31' AS Date), -3100, -3100, N'0')
23 rows affected

select * from sendMail


date
profit
ReviseP
flag


2019-10-01
-3200
-3200
0

2019-10-02
-3600
-3600
0

2019-10-03
-3000
-3000
0

2019-10-04
-4200
-4200
0

2019-10-07
2000
2000
0

2019-10-08
1000
1000
0

2019-10-09
1200
1200
0

2019-10-10
-2600
-2600
0

2019-10-11
-1800
-1800
0

2019-10-14
-3600
-3600
0

2019-10-15
-8200
-8200
0

2019-10-16
-6200
-6200
0

2019-10-17
-5000
-5000
0

2019-10-18
-3600
-3600
0

2019-10-21
2200
2200
0

2019-10-22
-3000
-3000
0

2019-10-23
-7100
-7100
0

2019-10-24
-8200
-8200
0

2019-10-25
-9600
-9600
0

2019-10-28
-12000
-12000
0

2019-10-29
-11000
-11000
0

2019-10-30
-8000
-8000
0

2019-10-31
-3100
-3100
0


declare @i int,
        @limit int 
set @i=1
set @limit= -3000
--i=1 , < -3000 ; i=2 , < -4000 ; i=3 , < -5000 ...一直到 i=8 , < -10000
--共執行8次

while(@i<9)
Begin
update t1
set t1.flag=1 , t1.ReviseP= @limit
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=@limit ) t2
where t1.date=t2.date and t2.num=1

Set @i=@i+1
Set @limit=@limit-1000  

END

set @i=1
set @limit= -10000

while(@i<24) --共執行23次 , 加上面 8次 , 最多執行31次
--當 每一次 @limit<-10000 都要寄 MAIL , 最壞情況31天都寄 MAIL 
Begin
update t1
set t1.flag=1 , t1.ReviseP= @limit
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=@limit and flag=0 ) t2
where t1.date=t2.date 
Set @i=@i+1   

END
9 rows affected

select * from sendMail


date
profit
ReviseP
flag


2019-10-01
-3200
-3000
1

2019-10-02
-3600
-3600
0

2019-10-03
-3000
-3000
0

2019-10-04
-4200
-4000
1

2019-10-07
2000
2000
0

2019-10-08
1000
1000
0

2019-10-09
1200
1200
0

2019-10-10
-2600
-2600
0

2019-10-11
-1800
-1800
0

2019-10-14
-3600
-3600
0

2019-10-15
-8200
-5000
1

2019-10-16
-6200
-6000
1

2019-10-17
-5000
-5000
0

2019-10-18
-3600
-3600
0

2019-10-21
2200
2200
0

2019-10-22
-3000
-3000
0

2019-10-23
-7100
-7000
1

2019-10-24
-8200
-8000
1

2019-10-25
-9600
-9000
1

2019-10-28
-12000
-10000
1

2019-10-29
-11000
-10000
1

2019-10-30
-8000
-8000
0

2019-10-31
-3100
-3100
0

date	profit	ReviseP
2019-10-01	-3200	-3200
2019-10-02	-3600	-3600
2019-10-03	-3000	-3000
2019-10-04	-4200	-4200
2019-10-07	2000	2000
2019-10-08	1000	1000
2019-10-09	1200	1200
2019-10-10	-2600	-2600
2019-10-11	-1800	-1800
2019-10-14	-3600	-3600
2019-10-15	-8200	-8200
2019-10-16	-6200	-6200
2019-10-17	-5000	-5000
2019-10-18	-3600	-3600
2019-10-21	2200	2200
2019-10-22	-3000	-3000
2019-10-23	-7100	-7100
2019-10-24	-8200	-8200
2019-10-25	-9600	-9600
2019-10-28	-12000	-12000
2019-10-29	-11000	-11000
2019-10-30	-8000	-8000
2019-10-31	-3100	-3100