iT邦幫忙

2

MSSQL , 連續達到條件之2 (原問題我已寫出來了) , 尋找迴圈寫法 或 Trigger

  • 分享至 

  • xImage

原問題連結 (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

https://ithelp.ithome.com.tw/upload/images/20191216/20123199VzfH1SHENn.jpg

根據我上面的 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

看更多先前的討論...收起先前的討論...
你是要月底才做判斷? 還是每天新增資料時判斷?
twyes iT邦新手 4 級 ‧ 2019-12-16 11:02:00 檢舉
ANS: 每天新增資料時判斷

每個月都要重新算 (每個月歸零重新來)(以月為單位)
其實這是滾動式的
每天都有新資料 (但我給的資料是整個月)

例如今天是 10月7日 , 則資料只會有 10月1日~7日
例如今天是 10月15日 , 則資料只會有 10月1日~15日
所以基本上是每天重新判斷 (基本上每個工作天都要run一次這個語法,然後挑選flag=1 and max(date) ,如果達到條件就觸發寄mail )

但我的寫法跟 rogeryao 寫法都可以 (每個工作天都重新執行判斷一次,這個寫法不會有問題) (不會影響結果)
之前跟你說了這是用trigger, 不過算了.你既然覺得你們這樣就可以,那就這樣吧.
twyes iT邦新手 4 級 ‧ 2019-12-16 11:32:30 檢舉
恩,但不用Trigger也可以,我已經寫好一段用 MSSQL 的 Database Mail 寄信的 SQL, 只要選擇 max(Date) and flag=1 就執行我寫好的用 Database Mail 寄信的 SQL , 寄出MAIL

主要是我沒用過 Trigger , 我再研究看看
twyes iT邦新手 4 級 ‧ 2019-12-16 15:19:50 檢舉
1
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友回答

立即登入回答