SQL-均線糾結+成交量大於5日均量3倍

select a.xDate , a.StockNo , b.Close_5MA ,c.Close_20MA , d.Close_55MA ,e.Volume_5MA, a.xVolume
from StockDailyHis2 as a
join Close_5MA as b on a.StockNo = b.StockNo and a.Date = b.Date
join Close_20MA as c on b.StockNo = c.StockNo and b.Date = c.Date
and ((cast(b.Close_5MA as float) / cast(c.Close_20MA as float) >=0.9)
or (cast(b.Close_5MA as float) / cast(c.Close_20MA as float) <=1.1))
join Close_55MA as d on b.StockNo = d.StockNo and b.Date = d.Date
and ((cast(b.Close_5MA as float) / cast(d.Close_55MA as float) >=0.9)
or (cast(b.Close_5MA as float) / cast(d.Close_55MA as float) <=1.1))

join Volume_5MA as e on a.StockNo = e.StockNo and a.Date = e.Date
and (cast(e.Volume_5MA as float) * 3 < cast(a.xVolume as float))

where a.xDate >= '20150101'
and cast(a.xVolume as float) > 1000
order by a.xDate , StockNo


紀錄: 9/11 跑出 2443,4526
過陣子再來看結果 XD

SQL - 連兩週千張大戶增幅超過1%

select a.StockNo ,a.DATE '本週' , a.Rate '本週持股比' , b.Rate '上週持股比',
round((cast(a.Rate as float) - cast(b.Rate as float)),2) '增幅%'
from
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )a,  --本周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )b,  --前一周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )c  --前兩周
where 1=1
and a.id0=b.id0+1
and b.id0=c.id0+1
and a.StockNo = b.StockNo
and a.StockNo = c.StockNo
and a.Date = '20150821'
and round((cast(a.Rate as float) - cast(b.Rate as float)),2) > 1
and round((cast(b.Rate as float) - cast(c.Rate as float)),2) > 1

SQL-如何透過MS SQL Server寄信(Gmail)

1.可先參考
http://www.dotblogs.com.tw/michaelchen/archive/2015/01/11/database_mail_use_gmail_stmp.aspx
進行基本設定

2.設定完畢後，若無法正常寄信，先檢查 "Database Mail Server 紀錄"
若出現 "Gmail 無法正常寄信 5.5.1 Authentication Required. Learn more ..." 的錯誤訊息，表示Gmail 帳號驗證不正確
可參考 http://demo.tc/post/807 設定 "Gmail應用程式密碼"

3.設定完畢後，再重新設定SQL Server Database Mail 之設定即可。

SQL-爆量長紅

select W.StockNo , W.Date , W.xOpen '開盤價', W.xClose '收盤價', W.xHigh '最高價', W.xLow '最低價',
W.xVolume '成交量', X.Volume_20MA '20MA成交量', W.Rate '漲跌幅'
from StockDailyHis as W
join Volume_20MA as X on W.Date = X.Date and W.StockNo = X.StockNo
where cast(W.Rate as float) > 4 --漲4%以上
and cast(W.xVolume as float) > 1000 --成交量千張以上
and cast(W.xVolume as float) > cast(X.Volume_20MA as float) *3  --今日成交量超過3倍20日均量
and W.xClose > W.xOpen --紅K
order by W.Date , W.StockNo

SQL-20日平均量

select A.StockNo , A.Date , (
select round(avg(cast(B.xVolume as float)),2)  from
(select top 20 cast(C.xVolume as float) xVolume from StockDailyHis C
  where C.Date<=A.Date and C.StockNo = A.StockNo order by C.Date desc) B
) Volume_20MA from StockDailyHis A where
1=1 and A.Date >= '20140101'
group by A.StockNo, A.Date
order by A.StockNo , A.Date

SQL-個股每日漲跌幅

select a.StockNo ,a.DATE '今日' , a.xClose '今日收盤價' , b.xClose '昨日收盤價',
round(((cast(a.xClose as float) - cast(b.xClose as float))/ cast(a.xClose as float)) * 100,2) '漲跌幅'
from
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )a,  --今天
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )b   --昨天
where 1=1
and a.id0=b.id0+1
and a.StockNo = b.StockNo
and a.Date >= '20140101'

SQL-收盤價55日均線

select A.Date , (
select round(avg(cast(B.xClose as float)),2) from
(select top 55 cast(C.xclose as float) xClose from StockDailyHis C
  where C.Date<=A.Date and C.StockNo = A.StockNo order by C.Date desc) B
) from StockDailyHis A where StockNo = '1234'

SQL-股價撈取跳空邏輯

select a.StockNo ,a.DATE '今日' , a.xLow '今日最低', b.xHigh '昨日最高' , a.xClose '今日收盤價' , b.xClose '昨日收盤價'
from
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )a,  --今天
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )b   --昨天
where
a.id0=b.id0+1
and cast(a.xLow as decimal) > cast(b.xHigh as decimal)
and a.StockNo = b.StockNo
and a.xClose >= a.xOpen --紅K
and a.Date >= '20150701'
and a.Date <= '20150731'
order by a.StockNo , a.Date