SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compounding Returns
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/16/2013 :  23:24:47  Show Profile  Reply with Quote
Is this what you want:

DECLARE @Temp TABLE (PortfolioID INT, [DATE] Date, OneMonthReturn INT);
INSERT INTO @Temp VALUES
(1, '2013-01-31', 5),
(1, '2013-02-28', 8),
(1, '2013-03-31', 1),
(1, '2013-04-30', 10),
(1, '2013-05-31', -5),
(1, '2013-06-30', -2);


; WITH CTE AS 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) AS RN, * FROM @Temp)
SELECT T1.PortfolioID, T1.[DATE], T1.OneMonthReturn, 
(CASE WHEN T1.RN >= 3 THEN ((1 + CAST(T1.OneMonthReturn AS NUMERIC(10,4))/100) * 
		          (1 + CAST(T2.OneMonthReturn AS NUMERIC(10,4))/100) * 
			 (1 + CAST(T3.OneMonthReturn AS NUMERIC(10,4))/100) - 1) ELSE 0 END)*100 AS ThreeMonthReturn
	FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN+2 LEFT JOIN CTE T3 ON T1.RN = T3.RN+1;



Edited by - MuMu88 on 06/16/2013 23:25:53
Go to Top of Page

Kapital123
Starting Member

29 Posts

Posted - 06/16/2013 :  23:37:59  Show Profile  Reply with Quote
Thanks alot mate. That works beautifully!

Thanks to you too visakh16.

Since this generates the 3 Month returns, I assume there is just a simple tweaking if I want say 6 month, 9 month and yearly returns?

quote:
Originally posted by MuMu88

Is this what you want:

DECLARE @Temp TABLE (PortfolioID INT, [DATE] Date, OneMonthReturn INT);
INSERT INTO @Temp VALUES
(1, '2013-01-31', 5),
(1, '2013-02-28', 8),
(1, '2013-03-31', 1),
(1, '2013-04-30', 10),
(1, '2013-05-31', -5),
(1, '2013-06-30', -2);


; WITH CTE AS 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) AS RN, * FROM @Temp)
SELECT T1.PortfolioID, T1.[DATE], T1.OneMonthReturn, 
(CASE WHEN T1.RN >= 3 THEN ((1 + CAST(T1.OneMonthReturn AS NUMERIC(10,4))/100) * 
		          (1 + CAST(T2.OneMonthReturn AS NUMERIC(10,4))/100) * 
			 (1 + CAST(T3.OneMonthReturn AS NUMERIC(10,4))/100) - 1) ELSE 0 END)*100 AS ThreeMonthReturn
	FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN+2 LEFT JOIN CTE T3 ON T1.RN = T3.RN+1;





Edited by - Kapital123 on 06/16/2013 23:39:15
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/16/2013 :  23:49:26  Show Profile  Reply with Quote
You can extend the query for six months as shown below; you can extend it for 9 months the same way ...



; WITH CTE AS 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) AS RN, * FROM @Temp)
SELECT T1.PortfolioID, T1.[DATE], T1.OneMonthReturn, 
(CASE WHEN T1.RN >= 6 THEN ((1 + CAST(T1.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T2.OneMonthReturn AS NUMERIC(10,4))/100) * 
			(1 + CAST(T3.OneMonthReturn AS NUMERIC(10,4))/100) *
			(1 + CAST(T4.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T5.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T6.OneMonthReturn AS NUMERIC(10,4))/100) - 1) ELSE 0 END)*100 AS SixMonthReturn
	FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN+5 LEFT JOIN CTE T3 ON T1.RN = T3.RN+4
	LEFT JOIN CTE T4 ON T1.RN = T4.RN+3 LEFT JOIN CTE T5 ON T1.RN = T5.RN+2 LEFT JOIN CTE T6 ON T1.RN = T6.RN+1;

Edited by - MuMu88 on 06/16/2013 23:57:26
Go to Top of Page

Kapital123
Starting Member

29 Posts

Posted - 06/17/2013 :  02:01:13  Show Profile  Reply with Quote
Brilliant, thankyou

quote:
Originally posted by MuMu88

You can extend the query for six months as shown below; you can extend it for 9 months the same way ...



; WITH CTE AS 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) AS RN, * FROM @Temp)
SELECT T1.PortfolioID, T1.[DATE], T1.OneMonthReturn, 
(CASE WHEN T1.RN >= 6 THEN ((1 + CAST(T1.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T2.OneMonthReturn AS NUMERIC(10,4))/100) * 
			(1 + CAST(T3.OneMonthReturn AS NUMERIC(10,4))/100) *
			(1 + CAST(T4.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T5.OneMonthReturn AS NUMERIC(10,4))/100) * 
		         (1 + CAST(T6.OneMonthReturn AS NUMERIC(10,4))/100) - 1) ELSE 0 END)*100 AS SixMonthReturn
	FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN+5 LEFT JOIN CTE T3 ON T1.RN = T3.RN+4
	LEFT JOIN CTE T4 ON T1.RN = T4.RN+3 LEFT JOIN CTE T5 ON T1.RN = T5.RN+2 LEFT JOIN CTE T6 ON T1.RN = T6.RN+1;


Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000