35 lines
1.1 KiB
SQL
35 lines
1.1 KiB
SQL
|
|
|
|
use market_data;
|
|
|
|
|
|
|
|
select sum(dh.cash_amount) cash_amount,count(*) records,YEAR(dh.payment_date) year from dividendhistory dh
|
|
where dh.symbol='gild' group by 3 order by 3 desc
|
|
|
|
|
|
SET @symbol='gild';
|
|
select A.*,B.cash_amount,B.records,(B.cash_amount/A.eps)*100.00 as dividend_load from
|
|
(select MIN(f.symbol) symbol,MIN(YEAR(f.asof)) year,avg(f.eps) eps from fundamentals f
|
|
where f.symbol=@symbol
|
|
group by YEAR(f.asof))A
|
|
inner join (
|
|
select MIN(symbol) symbol,sum(dh.cash_amount) cash_amount,count(*) records,YEAR(dh.payment_date) year from dividendhistory dh
|
|
where dh.symbol=@symbol group by 4
|
|
)B
|
|
on A.symbol=B.symbol and A.year=B.year
|
|
order by 2 desc
|
|
|
|
|
|
|
|
SET @symbol='cinr';
|
|
select A.symbol,A.year,A.eps,B.cash_amount,B.records,(B.cash_amount/A.eps)*100.00 as dividend_load_pct from
|
|
(select MIN(h.symbol) symbol,MIN(YEAR(h.asof)) year,min(h.value) eps from historical h
|
|
where h.symbol=@symbol and h.type='eps'
|
|
group by YEAR(h.asof))A
|
|
inner join (
|
|
select MIN(symbol) symbol,sum(dh.cash_amount) cash_amount,count(*) records,YEAR(dh.payment_date) year from dividendhistory dh
|
|
where dh.symbol=@symbol group by 4
|
|
)B
|
|
on A.symbol=B.symbol and A.year=B.year
|
|
order by 2 desc |