Compound interest query

Question

Post reply

Compound interest query

keymoo

SSCrazy

Points: 2975
More actions
October 16, 2010 at 3:21 am

#241730

Hi there,
I have a dataset of percent changes to an account but I don't have the account balance. What I want to see is an extra column with a cumulative compound return in percent for the dataset. Here's some T-SQL to create some sample data.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[percentChange]') AND type in (N'U'))
DROP TABLE [dbo].[percentChange]
GO
CREATE TABLE [dbo].[percentChange](
[id] [int] NOT NULL,
[orderDate] [date] NULL,
[pctAccountChange] [decimal](38, 6) NULL
)
GO
set nocount on
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(16,'2010-09-08',-0.711538)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(33,'2010-08-31',-0.914634)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(15,'2010-08-20',-0.919354)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(32,'2010-07-23',5.094488)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(14,'2010-07-14',2.636362)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(13,'2010-07-07',-1.012578)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(12,'2010-07-05',-0.145250)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(31,'2010-06-25',0.642156)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(30,'2010-06-18',-0.681318)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(29,'2010-06-08',-0.901162)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(11,'2010-05-11',2.570652)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(10,'2010-05-03',8.530000)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(28,'2010-05-03',8.554544)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(9,'2010-04-27',1.128000)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(27,'2010-04-26',-0.639784)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(26,'2010-04-20',-0.804122)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(8,'2010-04-20',3.047058)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(25,'2010-04-13',0.100718)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(7,'2010-04-06',2.153846)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(24,'2010-03-23',2.473684)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(6,'2010-03-19',0.418438)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(5,'2010-02-23',1.168674)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(23,'2010-02-23',1.074074)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(21,'2010-02-18',2.426966)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(4,'2010-02-15',-0.326796)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(20,'2010-02-04',2.225988)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(3,'2010-02-03',3.764226)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(19,'2010-01-29',1.379746)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(2,'2010-01-28',0.697986)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(1,'2010-01-19',1.375000)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(18,'2010-01-18',0.275862)
INSERT INTO [percentChange] ([id],[orderDate],[pctAccountChange])VALUES(17,'2010-01-07',-2.000000)
go
So for this dataset the last row should show a 51.4% compound change (if I've done my maths right in Excel, lol). I'm not sure how I would do this in T-SQL though. Please help, thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use the "quirky update" method.

Disclaimer:

DO NOT USE the code

until you've read Jeff's article[/url] and the related discussion.

DO NOT USE the code

until you completely understand how it works and why you need to follow ALL rules described in the article + the reason for the sequence check.

-- prepare the target table to hold the runningCompound value

ALTER TABLE [percentChange] ADD [runningComp] [DECIMAL](38, 6) NULL;

/*

IMPORTANT!!! create the clustered index to define the order of processing

If there is already a clustered index with a different order either replace it or verify

that this order can be used also to get the expected results.

A nonclustered index wil NOT help!!!

*/

CREATE CLUSTERED INDEX IX_percentChange_orderDate_id

ON [percentChange] (orderDate,id);

GO

-- define some variable to help with the quirky update

DECLARE

@runningValue [DECIMAL](38, 6), -- to hold the intermediate values

@Sequence INT -- verify that the Sequence of processing is the same as the reqired Sequence

SET @Sequence=0

SET @runningValue=1

;

WITH SafeTable AS

(

SELECT *,

ROW_NUMBER() OVER(ORDER BY orderDate,id) AS Sequence -- define the sequence for data processing

FROM [percentChange]

)

UPDATE SafeTable

SET

@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 END,-- double check to be still in sync

@runningValue = (@runningValue*(1+[pctAccountChange]/100)), -- calculate the new value

runningComp = (@runningValue-1)*100 -- store the value in the requested format

FROM SafeTable

WITH (TABLOCKX) -- IMPORTANT: lock the table to avoid data changes in between

OPTION (MAXDOP 1); -- avoid parallel processing to ensure proper sequence

SELECT *

FROM [percentChange]

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95409 More actions · Answer 2

LutzM (10/16/2010)
I would use the "quirky update" method.
Disclaimer:
DO NOT USE the code
until you've read Jeff's article[/url] and the related discussion.
DO NOT USE the code
until you completely understand how it works and why you need to follow ALL rules described in the article + the reason for the sequence check.
/*
IMPORTANT!!! create the clustered index to define the order of processing
If there is already a clustered index with a different order either replace it or verify
that this order can be used also to get the expected results.
A nonclustered index wil NOT help!!!
*/

One other note: if you already have a clustered index, and it does not have the necessary columns in the correct order, and you cannot change it, then you can still use this method. You will need to copy the data out to a #temp table, add the appropriate clustered index there, and then run the quirky update on the #temp table. If this is only for display purposes, then all you need to do is select the data out of the #temp table. If you need to put this data back into the normal table, you will need to update it by joining against the #temp table - so be sure that you copy out the Primary Key column(s) when making the #temp table so that you can join back to it at this step.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 3

LutzM (10/16/2010)
I would use the "quirky update" method.
Disclaimer:
DO NOT USE the code
until you've read Jeff's article[/url] and the related discussion.
DO NOT USE the code
until you completely understand how it works and why you need to follow ALL rules described in the article + the reason for the sequence check.
WITH SafeTable AS
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY orderDate,id) AS Sequence -- define the sequence for data processing
FROM [percentChange]
)
UPDATE SafeTable
SET
@Sequence = CASE WHEN Sequence = @Sequence + 1 THEN Sequence ELSE 1/0 END,-- double check to be still in sync

@Lutz: Cool...someone else is using the safety check now!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

keymoo SSCrazy Points: 2975 More actions · Answer 4

Thanks very much for your help. Exactly what I needed.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

keymoo (10/19/2010)
Thanks very much for your help. Exactly what I needed.

Just make sure you follow the rules and add the safety check that Lutz wrote about. The code being spoken of can be very useful but it can also be very dangerous unless it is used absolutely correctly. Take nothing for granted on that code. The article will be rewritten to incorporate the safety check but make sure you do it as a matter of rote.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)