原問題連結 (https://ithelp.ithome.com.tw/questions/10196509)
我的寫法如下 , 希望有人能夠寫出迴圈 或 Trigger
(原來rogeryao大大已經寫出解法了,最佳解答,剛才才看到)
(不知是否有人可以寫出迴圈的解法)
( <-10000 只發一次Mail , 或 <-10000每次都發Mail , 兩者都可以,看各位要寫哪一種解法)

資料準備

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')

解釋一下 剛開始 profit=ReviseP (修正profit)
隨著每一次判斷會修正 ReviseP 的值 , 以避免後續判斷錯誤
flag=1 代表要發MAIL , flag=0 代表不發mail

我的寫法下,尋求迴圈參數寫法 (把我的寫法變成迴圈參數) , 或者有神人要用另外的方式寫

--(1) < -3000
update t1
set t1.flag=1 , t1.ReviseP= -3000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-3000 ) t2
where t1.date=t2.date and t2.num=1



--(2) < -4000
update t1
set t1.flag=1 , t1.ReviseP= -4000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-4000 ) t2
where t1.date=t2.date and t2.num=1


--(3) < -5000
update t1
set t1.flag=1 , t1.ReviseP= -5000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-5000  ) t2
where t1.date=t2.date and t2.num=1


--(4) < -6000
update t1
set t1.flag=1 , t1.ReviseP= -6000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-6000 ) t2
where t1.date=t2.date and t2.num=1


--(5) < -7000
update t1
set t1.flag=1 , t1.ReviseP= -7000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-7000  ) t2
where t1.date=t2.date and t2.num=1

--(6) < -8000
update t1
set t1.flag=1 , t1.ReviseP= -8000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-8000 ) t2
where t1.date=t2.date and t2.num=1


--(7) < -9000
update t1
set t1.flag=1 , t1.ReviseP= -9000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-9000 ) t2
where t1.date=t2.date and t2.num=1


--(8) < -10000
update t1
set t1.flag=1 , t1.ReviseP= -10000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date) as num , *
    from sendMail
    where ReviseP<=-10000  ) t2
where t1.date=t2.date and t2.num=1


--(9) < -10000 (<-10000 出現超過2次以上)
update t1
set t1.flag=1 , t1.ReviseP= -10000
from sendMail t1 ,(
    select ROW_NUMBER () OVER(order by date ) as num , *
    from sendMail
    where ReviseP<=-10000 and flag=0 ) t2
where t1.date=t2.date and t2.num=1

根據我上面的 SQL
我把它寫成 迴圈
基本上最壞情況就是 31天通通寄mail (一個月最多31天)
前面 -3000 ~ -10000 共8天
後面每天都 < -10000 (假設 < -10000後 , 每天寄)

我把它寫成如下
關於迴圈的部分有沒有辦法調整成更簡潔
詳細如下

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