iT邦幫忙

1

MSSQL , 連續達到條件

日期 損益 要不要發mail
2019/10/1 -3200
2019/10/2 -3600 不發
2019/10/3 -3000 不發
2019/10/4 -4200
2019/10/7 2000 不發
2019/10/8 1000 不發
2019/10/9 1200 不發
2019/10/10 -2600 不發
2019/10/11 -1800 不發
2019/10/14 -3600 不發
2019/10/15 -8200
2019/10/16 -6200
2019/10/17 -5000 不發
2019/10/18 -3600 不發
2019/10/21 2200 不發
2019/10/22 -3000 不發
2019/10/23 -7100
2019/10/24 -8200
2019/10/25 -9600
2019/10/28 -12000
2019/10/29 -11000
2019/10/30 -8000 不發
2019/10/31 -3100 不發

說明:

  1. 這是每日算出來的匯率損益
    (基本上表格只有 日期 跟 損益,不會有 "要不要發這個欄位" )
    ("要不要發這個欄位" 是來附註規則的 , 這個欄位要自己判斷出來)

  2. 資料並非如以上是完整的一個月,例如今天是 2019/10/15
    則資料只會有 2019/10/1 ~ 2019/10/15
    (每一天的資料都只到今天) (Up to date)

  3. 每個月規則都歸零(月結),重新來

  4. 規則是
    如果第一次的損益小於 -3000 , 發 Email (10月1日)
    再下次損益 小於 -4000 ,才發 EMAIL(10月4日)
    再下次損益 小於 -5000 ,才發 EMAIL(10月15日)
    再下次損益 小於 -6000 ,才發 EMAIL(10月16日)
    再下次損益 小於 -7000 ,才發 EMAIL(10月23日)
    再下次損益 小於 -8000 ,才發 EMAIL(10月24日)
    再下次損益 小於 -9000 ,才發 EMAIL(10月25日)
    再下次損益 小於 -10000,才發 EMAIL(10月28日)
    再下次只要 小於 -10000 都發MAIL (10月29日) , 之後只要每次小於 -10000都發Mail
    每個月都重新計算

  5. 只要判斷要不要發 , 怎麼發MAIL已有解決方式
    亦即寫出上表 要不要發mail 的值 ("發" 或 "不發")

請問 MSSQL 怎麼寫 ?

(簡單講第一次達到條件發 , 第二次再達到條件發 , 第三次在達到條件發......)
(-3000,-4000,-5000,-6000,-7000 .........-10,000)
資料準備

declare @data table (
Data date,
損益 int,
要不要發mail nvarchar(10) )


insert into @data
values ('2019/10/1',	-3200,	'發' ) 
,('2019/10/2',	-3600,	'不發' ) 
,('2019/10/3',	-3000,	'不發' ) 
,('2019/10/4',	-4200,	'發' ) 
,('2019/10/7',	2000,'不發') 
,('2019/10/8',	1000,	'不發') 
,('2019/10/9',	1200,	'不發') 
,('2019/10/10',	-2600,	'不發' ) 
,('2019/10/11',	-1800,	'不發') 
,('2019/10/14',	-3600,	'不發') 
,('2019/10/15',	-8200,	'發') 
,('2019/10/16',	-6200,	'發' ) 
,('2019/10/17',	-5000,	'不發') 
,('2019/10/18',	-3600,	'不發') 
,('2019/10/21',	2200,	'不發' ) 
,('2019/10/22',	-3000,	'不發') 
,('2019/10/23',	-7100,	'發') 
,('2019/10/24',	-8200,	'發' ) 
,('2019/10/25',	-9600,	'發' ) 
,('2019/10/28',	-12000,	'發' ) 
,('2019/10/29',	-11000,'發') 
,('2019/10/30',	-8000,	'不發')  
,('2019/10/31',	-3100,	'不發') 

select * from @data
看更多先前的討論...收起先前的討論...
froce iT邦大師 1 級 ‧ 2019-12-13 15:57:39 檢舉
> 請問 MSSQL 怎麼寫 ?

發包給小魚大寫。XD
應該是要寫一個SQL,去判斷到底要不要發吧?
twyes iT邦新手 4 級 ‧ 2019-12-13 16:08:08 檢舉
twyes iT邦新手 4 級 ‧ 2019-12-13 16:11:26 檢舉
因為這是上面大頭要警戒的匯率損益,當虧損過多要發MAIL通知,但又不喜歡每天被發MAIL,所以設下要達到條件才發,只需要判斷出要不要發,怎麼發MAIL已經有方式
froce iT邦大師 1 級 ‧ 2019-12-13 16:23:09 檢舉
用程式比較好寫,不需要專門想用SQL寫。
當然這裡應該會有SQL的神人可以給你答案。
twyes iT邦新手 4 級 ‧ 2019-12-13 16:28:40 檢舉
這SQL應該寫得出來 , 就是因為這裡神人多才在這裡問 , 當然我自己也會想看看怎麼寫 (這個部分要用SQL寫 )
twyes iT邦新手 4 級 ‧ 2019-12-13 16:35:33 檢舉
有一個思考點,不要拿整個月的資料去思考寫SQL,要從今天(不一定是月底,可能是每個月的任一天)到月初的思考方式

譬如:10月1日已經達到條件要"發" ,給一個 flag=1 之後每天只要判斷 flag=1 and 今天的值 小於 -4000
當 -4000再達到時 給 flag=2 , 下次再判斷 flag=2 且今天 小於 -5000
因為他是滾動式的,可以逐日判斷,逐日update flag ,flag 紀錄在 Table
當然這只是想法
個人推測應該是 每日損益值決定後, 判斷以前的損益及根據損益發出mail紀錄,
上調發mail閥值,判斷損益與閥值後, insert into 當日發mail的紀錄.
這可以用 trigger 或是一般的程序性語言搭配,而不是單純流水的判斷一個月的紀錄.
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

1
rogeryao
iT邦超人 8 級 ‧ 2019-12-13 17:44:10
最佳解答

更新 SQL 如下 :

CREATE TABLE TEST(A date,B int,C nvarchar(10))
CREATE TABLE BASE(id int,M int)
insert into BASE
values (1,-3000) 
,(2,-4000) 
,(3,-5000) 
,(4,-6000) 
,(5,-7000) 
,(6,-8000) 
,(7,-9000) 
,(8,-10000) 

方法 1:

select A,B,
--TempC,
case when rownum=1 and TempC<>'' then N'發' else N'不發' end as Email
from (
select ROW_NUMBER () OVER(PARTITION BY TempC order by A) as rownum,A,B,TempC
from (
select A,B,
--TempA,TempB,
case when TempA>TempB then TempA else TempB end as TempC
from (
select A,B,TempA,
case 
when B <-3000 and B>=-4000 then -3000 
when B <-4000 and B>=-5000 then -4000 
when B <-5000 and B>=-6000 then -5000 
when B <-6000 and B>=-7000 then -6000 
when B <-7000 and B>=-8000 then -7000 
when B <-8000 and B>=-9000 then -8000 
when B <-9000 and B>=-10000 then -9000 
when B <-10000 then 10000 end as TempB
from TEST
left join (
select AA,max(M) as TempA
from (
select id,M,
(select 
min(A) 
from TEST
where 1=1
and B<M
) as AA
from BASE
) as TableX
where 1=1
group by AA
) as TableY on TEST.A=TableY.AA
) as TableZ
where 1=1
) as TableU
where 1=1
) as TableV
where 1=1
order by A

DEMO

方法 2:

select A,B,
--TempA,TempB,TempC,
case when rownum=1 and TempC<>'' then N'發' else N'不發' end as Email
from (
select ROW_NUMBER () OVER(PARTITION BY TempC order by A) as rownum,A,B,TempC
--,TempA,TempB
from (
select A,B,
case when TempA<TempB then TempB 
when TempA is null then TempB
else TempA end as TempC
--,TempA,TempB
from (
select A,B,min(M) as TempB
from TEST
left join BASE on 1=1
where 1=1
and B<M
group by A,B
--
union 
select  A,B,null as TempB
from TEST
where 1=1
and B>=-3000
) as TableD
--
left join (
select AA,max(M) as TempA
from (
select id,M,
(select 
min(A) 
from TEST
where 1=1
and B<M
) as AA
from BASE
) as TableX
where 1=1
group by AA
) as TableY on TableD.A=TableY.AA
) as TableZ
) as TableU
order by A

DEMO

twyes iT邦新手 4 級 ‧ 2019-12-13 21:25:24 檢舉

10月24日好像錯了 , 10月24日小於 -8000 , 應該是'發',但是你的解答好像是 '不發'

twyes iT邦新手 4 級 ‧ 2019-12-13 21:55:21 檢舉

我解釋一下
10月 1日發 , 因為 < -3000 (資料中的值是 -3200)
10月 4日發 , 因為 < -4000 (資料中的值是 -4200)
10月15日發 , 因為 < -5000 (資料中的值是 -8200)
10月16日發 , 因為 < -6000 (資料中的值是 -6200)
10月23日發 , 因為 < -7000 (資料中的值是 -7100)
10月24日發 , 因為 < -8000 (資料中的值是 -8200)
10月25日發 , 因為 < -9000 (資料中的值是 -9600)
10月28日發 , 因為 < -10000 (資料中的值是 -12000)
10月29日發 , 因為 < -10000 (資料中的值是 -11100)

我更正一下 10月29日不發 , 因為 < -10000 後發一次就好了

twyes iT邦新手 4 級 ‧ 2019-12-16 10:48:55 檢舉

感謝之前沒注意看 ,原來您已經寫出解法

我要發表回答

立即登入回答