Financial Returns...

[jared]

Hi Guys,

I am currently working on a putting together an sql query that
calculates, given an index, the percentage returns over a given period.

I have written the qeury that can calculate the percentage return.
However, the problem that I have is trying to combine these queries
into one large query that will return the date and the different
returns (1month, 3month, 6month......5year) at that point in time..

The following SQL returns the portfolio code, the date and the 3 month
return.

SELECT t1.PORTFOLIO_CODE, t1.DATE,
((t1.GROSS_INDEX-t2.GROSS_INDEX)/t2.GROSS_INDEX) AS 3MONTH_RETURN
FROM PORTFOLIO_PERFORMANCE AS t1
LEFT JOIN [SELECT PORTFOLIO_CODE, DATE AS ORIG_DATE_T2,
dateadd('d',-1,dateserial(Year(ORIG_DATE_T2), MONTH(ORIG_DATE_T2)+4,
1)) AS NEW_DATE_T2, GROSS_INDEX FROM PORTFOLIO_PERFORMANCE]. AS t2
ON (t1.DATE=t2.new_DATE_T2) AND (t1.PORTFOLIO_CODE=t2.PORTFOLIO_CODE);

I know that for each return, i need to effectively offset the table,
portfolio_performance with itself by the period im calculating the
returns for and then join them. but how do i do this multiple times?

Hope this question makes sense, any help would be appreciated, thanks
alot!!!