SQL

The following script contains the SQL statements for this query.

--Determine the yearly dividends and annual yield (dividends/average closing
--price) for the past 3 years for all the stocks in the Russell 2000 index that
--did not split during that period. Use unadjusted prices since there were no
--stock_split to adjust for.

commit
;

SELECT sh.TRADING_SYMBOL, DATEPART(yy,TRADE_DATE) AS YEAR, 
SUM(dividend_value)/AVG(CLOSE_PRICE) as DIVIDEND
FROM  MARKET_INDEX mi
inner join INDEX_CMPSTN AS ic
on mi.MARKET_INDEX_ID = ic.MARKET_INDEX_ID 
inner join STOCK_HISTORY AS sh
on ic.INSTRUMENT_ID = sh.INSTRUMENT_ID
AND sh.TRADE_DATE BETWEEN '2005-04-04' and '2008-04-03'
inner join DIVIDEND_EVENT de
on de.INSTRUMENT_ID= sh.INSTRUMENT_ID 
AND DATEPART(yy,TRADE_DATE)=DATEPART(yy,ANNOUNCED_DATE) 
AND de.INSTRUMENT_ID NOT IN (SELECT se.INSTRUMENT_ID
FROM   SPLIT_EVENT se
WHERE  sh.INSTRUMENT_ID=se.INSTRUMENT_ID 
AND   DATEPART(yy,TRADE_DATE)=
DATEPART(yy,EFFECTIVE_DATE)) 
WHERE mi.INDEX_NAME ='Russell 2000' 
GROUP  BY sh.TRADING_SYMBOL,
	DATEPART(yy,TRADE_DATE)
order by sh.TRADING_SYMBOL,
	DATEPART(yy,TRADE_DATE)
;

View this book as PDF