TWRR
Updated: 19 October 2012
Use TWRR to calculate the time-weighted rate of return. Time-weighted rate of return is a portfolio measurement statistic which can be used when there are movements of cash into and out of the portfolio.
TWRR is calculated using the follow formula:
Where
r is the time the time weighted rate of return
n is the period for which the return is calculated
MV is the ending market value for the period
P is the amount that has been added to the portfolio
S is the amount that has been subtracted from the portfolio
Syntax
SELECT [westclintech].[wct].[TWRR] (
<@CF, float,>
,<@CFDate, datetime),>)
,<@MV, bit),>)
Arguments
@CF
the cash flow amounts. @CF is an expression of type float or of a type that can be implicitly converted to float.
@CFDate
the date on which the cash flow occurred. @CFDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@MV
Identifies the cash flow as being the (ending) Market Value (‘True’). @MV is an expression of type bit or of a type that can be implicitly converted to bit.
Return Type
float
Remarks
· The TWRR aggregate function requires a series of cash flows (@CF) and the dates on which those cash flows occurred (@CFDate) as input. As a result, the order of the cash flows is not important.
· Dates on which the cash flow is zero, or on which there is no cash flow, do not have to be included.
· Beginning market values and additions to the portfolio should be entered as cash flows where the amount is greater than zero.
· Ending market values and withdrawals from the portfolio should be entered as cash flows where the amount is less than zero.
· There is no requirement to enter beginning market values. If the beginning market value for period is zero, the beginning market value is assumed to be the ending market values from the previous period (which must be passed to the function).
· There is no requirement to enter ending market values. If en ending market value is not entered for the day on which there is a cash movement, then the ending market value is obtained from the beginning market value for the next day for which there is a cash movement. If there is no cash movement for that day, then the ending market value is calculated as the beginning market values plus the purchases minus the sales.
Examples
The following table summarizes the activity and market values for a portfolio for a month.
|
Date
|
BMV
|
Purchases
|
Sales
|
EMV
|
|
2012-01-03
|
15,000.00
|
436.49
|
0.00
|
15,477.26
|
|
2012-01-10
|
15,477.26
|
0.00
|
-495.97
|
15,115.74
|
|
2012-01-17
|
15,115.74
|
297.92
|
-609.07
|
14,878.10
|
|
2012-01-24
|
14,878.10
|
157.06
|
-880.28
|
14,197.25
|
|
2012-01-31
|
14,197.25
|
0.00
|
0.00
|
14,275.08
|
To calculate the TWRR on this portfolio we will create and populate a temporary table.
CREATE TABLE #t(
trandate datetime,
trandescr varchar(50),
tranamt float
)
INSERT INTO #t VALUES ('2012-01-03','Beginning Market Value',15000)
INSERT INTO #t VALUES ('2012-01-03','Ending Market Value',-15477.26)
INSERT INTO #t VALUES ('2012-01-03','Purchases',436.49)
INSERT INTO #t VALUES ('2012-01-03','Sales',0)
INSERT INTO #t VALUES ('2012-01-10','Ending Market Value',-15115.74)
INSERT INTO #t VALUES ('2012-01-10','Purchases',0)
INSERT INTO #t VALUES ('2012-01-10','Sales',-495.97)
INSERT INTO #t VALUES ('2012-01-17','Ending Market Value',-14878.1)
INSERT INTO #t VALUES ('2012-01-17','Purchases',297.92)
INSERT INTO #t VALUES ('2012-01-17','Sales',-609.07)
INSERT INTO #t VALUES ('2012-01-24','Ending Market Value',-14197.25)
INSERT INTO #t VALUES ('2012-01-24','Purchases',157.06)
INSERT INTO #t VALUES ('2012-01-24','Sales',-880.28)
INSERT INTO #t VALUES ('2012-01-31','Ending Market Value',-14275.08)
INSERT INTO #t VALUES ('2012-01-31','Purchases',0)
INSERT INTO #t VALUES ('2012-01-31','Sales',0)
We can then calculate the time weighted rate of return durectly by invoking the function.
SELECT wct.TWRR(tranamt
,trandate
,CASE trandescr
WHEN 'Ending Market Value' THEN 'TRUE'
ELSE 'FALSE'
END
) as TWRR
FROM #t
This produces the following result
TWRR
----------------------
0.0246243629116802
The time weighted return for the month of January, 2012 is approximately 2.46%. This figure means that the performance of this portfolio is the same as the perfromance of a portfolio which had an opening balance of 100 on 2012-01-03 and a closing balance of 102.46 on 2012-01-31, with no cash movements in the portfolio.
Notice that @MV was set to 'TRUE' only for the ending market values and that only the initial beginning market value was passed into the transaction. In this case, the function used the ending market value from the previous period as the beginning market value for the current period.
Let’s look at slightly different presentation. Let’s assume that we actually have a table with this structure and we want to calculate the time-weighted rate of return.
|
Date
|
Purchases
|
Sales
|
EMV
|
|
2011-12-31
|
0.00
|
0.00
|
15,000.00
|
|
2012-01-03
|
436.49
|
0.00
|
15,477.26
|
|
2012-01-10
|
0.00
|
-495.97
|
15,115.74
|
|
2012-01-17
|
297.92
|
-609.07
|
14,878.10
|
|
2012-01-24
|
157.06
|
-880.28
|
14,197.25
|
|
2012-01-31
|
0.00
|
0.00
|
14,275.08
|
CREATE TABLE #t2(
tdate datetime,
purch money,
sales money,
emv money
)
INSERT INTO #t2 VALUES ('2011-12-31',0,0,15000.00)
INSERT INTO #t2 VALUES ('2012-01-03',436.49,0,15477.26)
INSERT INTO #t2 VALUES ('2012-01-10',0,495.97,15115.74)
INSERT INTO #t2 VALUES ('2012-01-17',297.92,609.07,14878.10)
INSERT INTO #t2 VALUES ('2012-01-24',157.06,880.28,14197.25)
INSERT INTO #t2 VALUES ('2012-01-31',0,0,14275.08)
We can then enter the following SQL to calculate the time-weighted rate of return.
SELECT wct.TWRR(cf,d,mv) as TWRR
FROM (
SELECT tdate, purch, 'FALSE' from #t2
UNION ALL
SELECT tdate, -sales, 'FALSE' from #t2
UNION ALL
SELECT tdate, -emv, 'TRUE' from #t2
) n(d, cf, mv)
This produces the following result.
TWRR
----------------------
0.0246243629116802
Given the following information, calculate the time –weighted rate of return for the portfolio for the first quarter.
|
Date
|
Market Value
|
Cash Flow
|
Market Value Post Cash Flow
|
|
2012-Dec-31
|
500,000
|
|
|
|
2013-Jan-31
|
509,000
|
|
|
|
2013-Feb-19
|
513,000
|
+50,000
|
563,000
|
|
2013-Feb-28
|
575,000
|
|
|
|
2013-Mar-12
|
585,000
|
-20,000
|
575,000
|
|
2013-Mar-31
|
570,000
|
|
|
We could enter the following SQL to perform the calculation.
SELECT wct.TWRR(cf,cfdate,mv) as TWRR
FROM (VALUES
(-500000,'2012-12-31','True'),
(-509000,'2013-01-31','True'),
(513000,'2013-02-19','True'),
(50000,'2013-02-19','False'),
(-575000,'2013-02-28','True'),
(585000,'2013-03-12','True'),
(-20000,'2013-03-12','False'),
(-570000,'2013-03-31','True')
)n(cf,cfdate,mv)
This produces the following result.
TWRR
----------------------
0.0755268080290477
Note, that we did not include the ending market values for 2013-Feb-19 and 2013-Mar-12, but only because the ending market value was equal to the market value plus the cash flow. Here’s the SQL with those ending market values included, which produces exactly the same result.
SELECT wct.TWRR(cf,cfdate,mv) as TWRR
FROM (VALUES
(-500000,'2012-12-31','True'),
(-509000,'2013-01-31','True'),
(513000,'2013-02-19','True'),
(50000,'2013-02-19','False'),
(-575000,'2013-02-28','True'),
(585000,'2013-03-12','True'),
(-20000,'2013-03-12','False'),
(-570000,'2013-03-31','True'),
(-563000,'2013-02-19','True'),
(-565000,'2013-03-12','True')
)n(cf,cfdate,mv)
This produces the following result.
TWRR
----------------------
0.0755268080290477
Let’s look at another example, where we have a transaction table, containing all the purchase and sale transactions, and a market value table, which holds the ending market value for each day. We will continue to assume that all the transactions are for a single account or portfolio.
/*A transaction table*/
CREATE TABLE #t3(
TranNo float,
TranDate datetime,
TranAmt money,
PS char(1)
)
/*Insert rows into the transaction table*/
INSERT INTO #t3 VALUES (1,'2012-01-24',443.03,'S')
INSERT INTO #t3 VALUES (2,'2012-01-04',206.68,'P')
INSERT INTO #t3 VALUES (3,'2012-01-30',230.45,'S')
INSERT INTO #t3 VALUES (4,'2012-01-21',123.94,'P')
INSERT INTO #t3 VALUES (5,'2012-01-24',18.41,'P')
INSERT INTO #t3 VALUES (6,'2012-01-11',323.42,'P')
INSERT INTO #t3 VALUES (7,'2012-01-06',321.79,'P')
INSERT INTO #t3 VALUES (8,'2012-01-30',141.03,'P')
INSERT INTO #t3 VALUES (9,'2012-01-06',454.14,'P')
INSERT INTO #t3 VALUES (10,'2012-01-30',495.57,'S')
INSERT INTO #t3 VALUES (11,'2012-01-25',77.56,'P')
INSERT INTO #t3 VALUES (12,'2012-01-27',94.72,'P')
INSERT INTO #t3 VALUES (13,'2012-01-05',331.05,'P')
INSERT INTO #t3 VALUES (14,'2012-01-26',382.78,'S')
INSERT INTO #t3 VALUES (15,'2012-01-25',399.07,'S')
INSERT INTO #t3 VALUES (16,'2012-01-13',49.72,'S')
INSERT INTO #t3 VALUES (17,'2012-01-15',159.08,'S')
INSERT INTO #t3 VALUES (18,'2012-01-16',494.56,'P')
INSERT INTO #t3 VALUES (19,'2012-01-30',442.46,'P')
INSERT INTO #t3 VALUES (20,'2012-01-30',326.42,'P')
INSERT INTO #t3 VALUES (21,'2012-01-13',54.31,'P')
INSERT INTO #t3 VALUES (22,'2012-01-04',448.36,'S')
INSERT INTO #t3 VALUES (23,'2012-01-26',491.41,'P')
INSERT INTO #t3 VALUES (24,'2012-01-28',110.53,'S')
INSERT INTO #t3 VALUES (25,'2012-01-17',255.22,'P')
INSERT INTO #t3 VALUES (26,'2012-01-19',386.82,'P')
INSERT INTO #t3 VALUES (27,'2012-01-08',334.43,'S')
INSERT INTO #t3 VALUES (28,'2012-01-03',434.05,'P')
INSERT INTO #t3 VALUES (29,'2012-01-16',213.43,'P')
INSERT INTO #t3 VALUES (30,'2012-01-16',377.25,'P')
INSERT INTO #t3 VALUES (31,'2012-01-30',369.98,'S')
INSERT INTO #t3 VALUES (32,'2012-01-28',79.15,'S')
INSERT INTO #t3 VALUES (33,'2012-01-30',7.59,'S')
INSERT INTO #t3 VALUES (34,'2012-01-25',375.2,'P')
INSERT INTO #t3 VALUES (35,'2012-01-19',457.77,'S')
INSERT INTO #t3 VALUES (36,'2012-01-09',133.37,'P')
INSERT INTO #t3 VALUES (37,'2012-01-09',348.05,'S')
INSERT INTO #t3 VALUES (38,'2012-01-26',102.9,'P')
INSERT INTO #t3 VALUES (39,'2012-01-28',327.83,'S')
INSERT INTO #t3 VALUES (40,'2012-01-26',430.54,'S')
INSERT INTO #t3 VALUES (41,'2012-01-15',295.55,'P')
INSERT INTO #t3 VALUES (42,'2012-01-22',287.58,'S')
INSERT INTO #t3 VALUES (43,'2012-01-24',177,'P')
INSERT INTO #t3 VALUES (44,'2012-01-22',244.55,'S')
INSERT INTO #t3 VALUES (45,'2012-01-19',103.37,'P')
INSERT INTO #t3 VALUES (46,'2012-01-05',30.41,'P')
INSERT INTO #t3 VALUES (47,'2012-01-03',198.96,'P')
INSERT INTO #t3 VALUES (48,'2012-01-20',133.89,'P')
INSERT INTO #t3 VALUES (49,'2012-01-17',279.8,'S')
INSERT INTO #t3 VALUES (50,'2012-01-04',303.53,'P')
INSERT INTO #t3 VALUES (51,'2012-01-12',19.03,'P')
INSERT INTO #t3 VALUES (52,'2012-01-03',43.41,'S')
INSERT INTO #t3 VALUES (53,'2012-01-10',153.24,'S')
INSERT INTO #t3 VALUES (54,'2012-01-26',355.44,'P')
INSERT INTO #t3 VALUES (55,'2012-01-23',498.07,'P')
INSERT INTO #t3 VALUES (56,'2012-01-28',408.11,'P')
INSERT INTO #t3 VALUES (57,'2012-01-18',158.1,'S')
INSERT INTO #t3 VALUES (58,'2012-01-23',135.44,'S')
INSERT INTO #t3 VALUES (59,'2012-01-20',118.38,'S')
INSERT INTO #t3 VALUES (60,'2012-01-16',234.17,'P')
INSERT INTO #t3 VALUES (61,'2012-01-08',131.35,'P')
INSERT INTO #t3 VALUES (62,'2012-01-11',107.71,'P')
INSERT INTO #t3 VALUES (63,'2012-01-11',48.28,'P')
INSERT INTO #t3 VALUES (64,'2012-01-10',42.11,'P')
INSERT INTO #t3 VALUES (65,'2012-01-14',395.03,'P')
INSERT INTO #t3 VALUES (66,'2012-01-11',173.67,'S')
INSERT INTO #t3 VALUES (67,'2012-01-21',98.8,'P')
INSERT INTO #t3 VALUES (68,'2012-01-16',452.9,'P')
INSERT INTO #t3 VALUES (69,'2012-01-22',468.29,'S')
INSERT INTO #t3 VALUES (70,'2012-01-12',339.25,'S')
INSERT INTO #t3 VALUES (71,'2012-01-05',165.75,'P')
INSERT INTO #t3 VALUES (72,'2012-01-13',403.86,'S')
INSERT INTO #t3 VALUES (73,'2012-01-22',86.25,'P')
INSERT INTO #t3 VALUES (74,'2012-01-04',389.18,'S')
INSERT INTO #t3 VALUES (75,'2012-01-20',486.33,'S')
INSERT INTO #t3 VALUES (76,'2012-01-16',234.62,'P')
INSERT INTO #t3 VALUES (77,'2012-01-07',339.63,'S')
INSERT INTO #t3 VALUES (78,'2012-01-14',124.5,'P')
INSERT INTO #t3 VALUES (79,'2012-01-11',98.72,'P')
INSERT INTO #t3 VALUES (80,'2012-01-06',347.27,'P')
INSERT INTO #t3 VALUES (81,'2012-01-07',487.62,'P')
INSERT INTO #t3 VALUES (82,'2012-01-08',280.18,'P')
INSERT INTO #t3 VALUES (83,'2012-01-22',205.78,'S')
INSERT INTO #t3 VALUES (84,'2012-01-09',154.41,'S')
INSERT INTO #t3 VALUES (85,'2012-01-22',11.84,'S')
INSERT INTO #t3 VALUES (86,'2012-01-07',464.59,'P')
INSERT INTO #t3 VALUES (87,'2012-01-20',323.95,'S')
INSERT INTO #t3 VALUES (88,'2012-01-26',131.63,'S')
INSERT INTO #t3 VALUES (89,'2012-01-16',32.9,'S')
INSERT INTO #t3 VALUES (90,'2012-01-03',404.52,'P')
INSERT INTO #t3 VALUES (91,'2012-01-24',420.44,'S')
INSERT INTO #t3 VALUES (92,'2012-01-12',468.48,'P')
INSERT INTO #t3 VALUES (93,'2012-01-03',482.73,'P')
INSERT INTO #t3 VALUES (94,'2012-01-22',72.64,'S')
INSERT INTO #t3 VALUES (95,'2012-01-22',15.38,'S')
INSERT INTO #t3 VALUES (96,'2012-01-05',413.72,'P')
INSERT INTO #t3 VALUES (97,'2012-01-12',217.6,'S')
INSERT INTO #t3 VALUES (98,'2012-01-31',317.93,'P')
INSERT INTO #t3 VALUES (99,'2012-01-04',68.58,'S')
INSERT INTO #t3 VALUES (100,'2012-01-08',330.72,'S')
INSERT INTO #t3 VALUES (101,'2012-01-25',45.26,'S')
/*A market value table*/
CREATE TABLE #mv(
trandate datetime,
emv money
)
/*Insert Rows into the market value table*/
INSERT INTO #mv VALUES ('2011-12-31',15000)
INSERT INTO #mv VALUES ('2012-01-03',16566.31)
INSERT INTO #mv VALUES ('2012-01-04',16222.72)
INSERT INTO #mv VALUES ('2012-01-05',17058.02)
INSERT INTO #mv VALUES ('2012-01-06',18213.62)
INSERT INTO #mv VALUES ('2012-01-07',18698.79)
INSERT INTO #mv VALUES ('2012-01-08',18357.06)
INSERT INTO #mv VALUES ('2012-01-09',17972.14)
INSERT INTO #mv VALUES ('2012-01-10',17708.68)
INSERT INTO #mv VALUES ('2012-01-11',17944.47)
INSERT INTO #mv VALUES ('2012-01-12',17745.1)
INSERT INTO #mv VALUES ('2012-01-13',17317.47)
INSERT INTO #mv VALUES ('2012-01-14',17765.5)
INSERT INTO #mv VALUES ('2012-01-15',18051.69)
INSERT INTO #mv VALUES ('2012-01-16',20167.64)
INSERT INTO #mv VALUES ('2012-01-17',20229.2)
INSERT INTO #mv VALUES ('2012-01-18',20114.92)
INSERT INTO #mv VALUES ('2012-01-19',20117.91)
INSERT INTO #mv VALUES ('2012-01-20',19376.21)
INSERT INTO #mv VALUES ('2012-01-21',19518.13)
INSERT INTO #mv VALUES ('2012-01-22',18170.05)
INSERT INTO #mv VALUES ('2012-01-23',18502.23)
INSERT INTO #mv VALUES ('2012-01-24',17736.57)
INSERT INTO #mv VALUES ('2012-01-25',17882.04)
INSERT INTO #mv VALUES ('2012-01-26',17988.62)
INSERT INTO #mv VALUES ('2012-01-27',17934.44)
INSERT INTO #mv VALUES ('2012-01-28',17730.44)
INSERT INTO #mv VALUES ('2012-01-30',17661.15)
INSERT INTO #mv VALUES ('2012-01-31',17901.49)
/*Calculate the time-weighted rate of return*/
SELECT wct.TWRR(c,d,mv) as TWRR
FROM (
SELECT Trandate
,CASE PS
WHEN 'P' THEN TranAmt
ELSE -TranAmt
END
,'False'
FROM #t3
UNION ALL
SELECT Trandate
,-emv
,'True'
FROM #mv
) n(d, c, mv)
This produces that following result.
TWRR
----------------------
-0.0309539334921339
Finally, lets look at an example where we have many purchase and sale transactions across many accounts. As in the previous example, we will have the transactions and the market values in separate tables.
/*A transaction table*/
CREATE TABLE #t4(
Account float,
TranNo float,
TranDate datetime,
TranAmt money,
PS char(1)
)
/*Insert rows into the transaction table*/
INSERT INTO #t4 VALUES (5,1,'2012-01-07',193,'P')
INSERT INTO #t4 VALUES (4,2,'2012-01-03',433.94,'S')
INSERT INTO #t4 VALUES (7,3,'2012-01-27',375.67,'P')
INSERT INTO #t4 VALUES (4,4,'2012-01-29',259.3,'P')
INSERT INTO #t4 VALUES (5,5,'2012-01-20',265.77,'P')
INSERT INTO #t4 VALUES (2,6,'2012-01-16',140.77,'P')
INSERT INTO #t4 VALUES (5,7,'2012-01-04',331.01,'P')
INSERT INTO #t4 VALUES (7,8,'2012-01-16',192.06,'S')
INSERT INTO #t4 VALUES (8,9,'2012-01-21',181.18,'S')
INSERT INTO #t4 VALUES (6,10,'2012-01-17',283.65,'P')
INSERT INTO #t4 VALUES (1,11,'2012-01-18',238.66,'S')
INSERT INTO #t4 VALUES (10,12,'2012-01-14',454.69,'P')
INSERT INTO #t4 VALUES (4,13,'2012-01-13',49.13,'P')
INSERT INTO #t4 VALUES (2,14,'2012-01-25',393.34,'P')
INSERT INTO #t4 VALUES (10,15,'2012-01-17',280.07,'S')
INSERT INTO #t4 VALUES (4,16,'2012-01-07',278.37,'P')
INSERT INTO #t4 VALUES (5,17,'2012-01-23',150.21,'S')
INSERT INTO #t4 VALUES (3,18,'2012-01-24',157.86,'S')
INSERT INTO #t4 VALUES (8,19,'2012-01-06',420.21,'P')
INSERT INTO #t4 VALUES (6,20,'2012-01-05',416.18,'P')
INSERT INTO #t4 VALUES (1,21,'2012-01-27',177.74,'S')
INSERT INTO #t4 VALUES (2,22,'2012-01-05',112.99,'P')
INSERT INTO #t4 VALUES (3,23,'2012-01-21',213.14,'P')
INSERT INTO #t4 VALUES (4,24,'2012-01-10',26.14,'P')
INSERT INTO #t4 VALUES (2,25,'2012-01-21',403.89,'S')
INSERT INTO #t4 VALUES (6,26,'2012-01-05',402.07,'P')
INSERT INTO #t4 VALUES (7,27,'2012-01-26',172.23,'P')
INSERT INTO #t4 VALUES (10,28,'2012-01-06',57.14,'S')
INSERT INTO #t4 VALUES (6,29,'2012-01-24',456.4,'P')
INSERT INTO #t4 VALUES (7,30,'2012-01-03',82.61,'P')
INSERT INTO #t4 VALUES (5,31,'2012-01-23',312.75,'S')
INSERT INTO #t4 VALUES (3,32,'2012-01-31',324.15,'P')
INSERT INTO #t4 VALUES (4,33,'2012-01-15',425.61,'P')
INSERT INTO #t4 VALUES (4,34,'2012-01-10',26.97,'S')
INSERT INTO #t4 VALUES (2,35,'2012-01-16',13.58,'S')
INSERT INTO #t4 VALUES (2,36,'2012-01-22',359.44,'P')
INSERT INTO #t4 VALUES (2,37,'2012-01-16',169.76,'P')
INSERT INTO #t4 VALUES (2,38,'2012-01-07',185.33,'P')
INSERT INTO #t4 VALUES (10,39,'2012-01-26',308.47,'P')
INSERT INTO #t4 VALUES (10,40,'2012-01-20',259.13,'P')
INSERT INTO #t4 VALUES (1,41,'2012-01-11',170.75,'S')
INSERT INTO #t4 VALUES (1,42,'2012-01-30',289.28,'S')
INSERT INTO #t4 VALUES (7,43,'2012-01-11',479.04,'P')
INSERT INTO #t4 VALUES (7,44,'2012-01-17',414.43,'S')
INSERT INTO #t4 VALUES (1,45,'2012-01-21',426.68,'P')
INSERT INTO #t4 VALUES (1,46,'2012-01-29',409.32,'S')
INSERT INTO #t4 VALUES (5,47,'2012-01-26',482.93,'P')
INSERT INTO #t4 VALUES (6,48,'2012-01-21',34.37,'S')
INSERT INTO #t4 VALUES (9,49,'2012-01-25',413.87,'S')
INSERT INTO #t4 VALUES (5,50,'2012-01-25',433.75,'S')
INSERT INTO #t4 VALUES (1,51,'2012-01-24',86.32,'S')
INSERT INTO #t4 VALUES (2,52,'2012-01-03',299.93,'S')
INSERT INTO #t4 VALUES (3,53,'2012-01-29',313.76,'P')
INSERT INTO #t4 VALUES (10,54,'2012-01-11',221.75,'S')
INSERT INTO #t4 VALUES (10,55,'2012-01-22',217.94,'S')
INSERT INTO #t4 VALUES (3,56,'2012-01-15',0.89,'P')
INSERT INTO #t4 VALUES (1,57,'2012-01-09',301.44,'P')
INSERT INTO #t4 VALUES (2,58,'2012-01-18',479.71,'S')
INSERT INTO #t4 VALUES (8,59,'2012-01-21',217.12,'P')
INSERT INTO #t4 VALUES (3,60,'2012-01-04',310.77,'P')
INSERT INTO #t4 VALUES (8,61,'2012-01-12',82.97,'S')
INSERT INTO #t4 VALUES (9,62,'2012-01-03',337.39,'P')
INSERT INTO #t4 VALUES (5,63,'2012-01-11',300.44,'P')
INSERT INTO #t4 VALUES (3,64,'2012-01-17',181.82,'P')
INSERT INTO #t4 VALUES (3,65,'2012-01-23',160.31,'P')
INSERT INTO #t4 VALUES (1,66,'2012-01-11',370.76,'P')
INSERT INTO #t4 VALUES (6,67,'2012-01-15',485.3,'P')
INSERT INTO #t4 VALUES (7,68,'2012-01-23',359.27,'P')
INSERT INTO #t4 VALUES (9,69,'2012-01-29',435.07,'S')
INSERT INTO #t4 VALUES (8,70,'2012-01-11',172.44,'P')
INSERT INTO #t4 VALUES (6,71,'2012-01-15',387.01,'P')
INSERT INTO #t4 VALUES (3,72,'2012-01-21',451.6,'P')
INSERT INTO #t4 VALUES (8,73,'2012-01-20',447.41,'S')
INSERT INTO #t4 VALUES (2,74,'2012-01-19',239.09,'P')
INSERT INTO #t4 VALUES (4,75,'2012-01-08',31.82,'S')
INSERT INTO #t4 VALUES (2,76,'2012-01-24',0.5,'P')
INSERT INTO #t4 VALUES (9,77,'2012-01-06',368.34,'S')
INSERT INTO #t4 VALUES (8,78,'2012-01-13',373.48,'S')
INSERT INTO #t4 VALUES (10,79,'2012-01-08',145.68,'P')
INSERT INTO #t4 VALUES (8,80,'2012-01-14',421.34,'P')
INSERT INTO #t4 VALUES (3,81,'2012-01-27',426.32,'S')
INSERT INTO #t4 VALUES (6,82,'2012-01-19',498.18,'P')
INSERT INTO #t4 VALUES (1,83,'2012-01-28',227.59,'P')
INSERT INTO #t4 VALUES (2,84,'2012-01-22',132.22,'P')
INSERT INTO #t4 VALUES (5,85,'2012-01-28',199.96,'S')
INSERT INTO #t4 VALUES (2,86,'2012-01-22',128.54,'S')
INSERT INTO #t4 VALUES (2,87,'2012-01-08',54.64,'S')
INSERT INTO #t4 VALUES (10,88,'2012-01-21',325.61,'S')
INSERT INTO #t4 VALUES (7,89,'2012-01-14',97.29,'P')
INSERT INTO #t4 VALUES (6,90,'2012-01-26',280.88,'S')
INSERT INTO #t4 VALUES (6,91,'2012-01-05',343.55,'S')
INSERT INTO #t4 VALUES (2,92,'2012-01-22',401.53,'P')
INSERT INTO #t4 VALUES (4,93,'2012-01-23',3.8,'P')
INSERT INTO #t4 VALUES (5,94,'2012-01-13',216.94,'S')
INSERT INTO #t4 VALUES (9,95,'2012-01-09',137.85,'S')
INSERT INTO #t4 VALUES (6,96,'2012-01-23',252.26,'P')
INSERT INTO #t4 VALUES (1,97,'2012-01-29',413.12,'S')
INSERT INTO #t4 VALUES (2,98,'2012-01-11',156.34,'S')
INSERT INTO #t4 VALUES (5,99,'2012-01-16',471.37,'P')
INSERT INTO #t4 VALUES (1,100,'2012-01-13',31.22,'P')
INSERT INTO #t4 VALUES (3,101,'2012-01-04',190.55,'P')
INSERT INTO #t4 VALUES (2,102,'2012-01-19',220.66,'S')
INSERT INTO #t4 VALUES (2,103,'2012-01-16',361.22,'P')
INSERT INTO #t4 VALUES (6,104,'2012-01-16',11.26,'S')
INSERT INTO #t4 VALUES (1,105,'2012-01-12',493.1,'S')
INSERT INTO #t4 VALUES (2,106,'2012-01-08',120.85,'S')
INSERT INTO #t4 VALUES (2,107,'2012-01-06',469.36,'S')
INSERT INTO #t4 VALUES (2,108,'2012-01-19',95.96,'P')
INSERT INTO #t4 VALUES (3,109,'2012-01-09',140.68,'P')
INSERT INTO #t4 VALUES (10,110,'2012-01-10',300.67,'P')
INSERT INTO #t4 VALUES (3,111,'2012-01-11',234.91,'S')
INSERT INTO #t4 VALUES (3,112,'2012-01-06',84.08,'S')
INSERT INTO #t4 VALUES (2,113,'2012-01-23',72.69,'P')
INSERT INTO #t4 VALUES (3,114,'2012-01-17',95.02,'S')
INSERT INTO #t4 VALUES (8,115,'2012-01-21',186.21,'P')
INSERT INTO #t4 VALUES (3,116,'2012-01-22',142.4,'P')
INSERT INTO #t4 VALUES (2,117,'2012-01-27',29.39,'S')
INSERT INTO #t4 VALUES (1,118,'2012-01-28',294.85,'S')
INSERT INTO #t4 VALUES (7,119,'2012-01-25',419.66,'S')
INSERT INTO #t4 VALUES (9,120,'2012-01-16',198.61,'S')
INSERT INTO #t4 VALUES (7,121,'2012-01-20',169.37,'P')
INSERT INTO #t4 VALUES (2,122,'2012-01-21',491.1,'P')
INSERT INTO #t4 VALUES (1,123,'2012-01-08',420.79,'S')
INSERT INTO #t4 VALUES (8,124,'2012-01-24',79.3,'S')
INSERT INTO #t4 VALUES (7,125,'2012-01-05',318.8,'P')
INSERT INTO #t4 VALUES (2,126,'2012-01-16',7.31,'S')
INSERT INTO #t4 VALUES (6,127,'2012-01-14',171.45,'P')
INSERT INTO #t4 VALUES (3,128,'2012-01-22',425.94,'S')
INSERT INTO #t4 VALUES (1,129,'2012-01-16',22.1,'P')
INSERT INTO #t4 VALUES (6,130,'2012-01-05',105.81,'P')
INSERT INTO #t4 VALUES (9,131,'2012-01-28',450.53,'P')
INSERT INTO #t4 VALUES (1,132,'2012-01-15',94.24,'S')
INSERT INTO #t4 VALUES (5,133,'2012-01-20',365.81,'P')
INSERT INTO #t4 VALUES (1,134,'2012-01-24',250.44,'P')
INSERT INTO #t4 VALUES (3,135,'2012-01-23',174.05,'S')
INSERT INTO #t4 VALUES (3,136,'2012-01-11',364.29,'P')
INSERT INTO #t4 VALUES (3,137,'2012-01-09',337.16,'S')
INSERT INTO #t4 VALUES (9,138,'2012-01-24',30.75,'S')
INSERT INTO #t4 VALUES (9,139,'2012-01-24',173.39,'S')
INSERT INTO #t4 VALUES (8,140,'2012-01-26',280.12,'P')
INSERT INTO #t4 VALUES (4,141,'2012-01-27',85.37,'P')
INSERT INTO #t4 VALUES (1,142,'2012-01-14',52.67,'S')
INSERT INTO #t4 VALUES (9,143,'2012-01-04',72.56,'P')
INSERT INTO #t4 VALUES (7,144,'2012-01-09',86.86,'P')
INSERT INTO #t4 VALUES (10,145,'2012-01-03',487.51,'S')
INSERT INTO #t4 VALUES (9,146,'2012-01-21',433.26,'P')
INSERT INTO #t4 VALUES (9,147,'2012-01-21',139.6,'P')
INSERT INTO #t4 VALUES (7,148,'2012-01-12',8.85,'P')
INSERT INTO #t4 VALUES (9,149,'2012-01-15',331.08,'P')
INSERT INTO #t4 VALUES (4,150,'2012-01-22',138.73,'P')
INSERT INTO #t4 VALUES (1,151,'2012-01-07',292.69,'S')
INSERT INTO #t4 VALUES (2,152,'2012-01-12',166.4,'P')
INSERT INTO #t4 VALUES (9,153,'2012-01-08',95.82,'P')
INSERT INTO #t4 VALUES (8,154,'2012-01-26',467.95,'S')
INSERT INTO #t4 VALUES (7,155,'2012-01-04',467.55,'P')
INSERT INTO #t4 VALUES (10,156,'2012-01-24',157.61,'S')
INSERT INTO #t4 VALUES (10,157,'2012-01-13',301.34,'S')
INSERT INTO #t4 VALUES (4,158,'2012-01-04',282.53,'P')
INSERT INTO #t4 VALUES (1,159,'2012-01-23',11.11,'P')
INSERT INTO #t4 VALUES (2,160,'2012-01-08',298.94,'P')
INSERT INTO #t4 VALUES (8,161,'2012-01-29',385.65,'P')
INSERT INTO #t4 VALUES (4,162,'2012-01-25',326.45,'P')
INSERT INTO #t4 VALUES (3,163,'2012-01-12',113.98,'P')
INSERT INTO #t4 VALUES (10,164,'2012-01-24',361.17,'P')
INSERT INTO #t4 VALUES (2,165,'2012-01-19',211.64,'S')
INSERT INTO #t4 VALUES (9,166,'2012-01-05',244.2,'P')
INSERT INTO #t4 VALUES (10,167,'2012-01-18',110.39,'P')
INSERT INTO #t4 VALUES (8,168,'2012-01-20',297.8,'P')
INSERT INTO #t4 VALUES (10,169,'2012-01-11',295.05,'P')
INSERT INTO #t4 VALUES (7,170,'2012-01-28',299.23,'P')
INSERT INTO #t4 VALUES (3,171,'2012-01-25',203.88,'P')
INSERT INTO #t4 VALUES (6,172,'2012-01-28',115.1,'P')
INSERT INTO #t4 VALUES (7,173,'2012-01-04',425.18,'S')
INSERT INTO #t4 VALUES (1,174,'2012-01-05',63.76,'S')
INSERT INTO #t4 VALUES (2,175,'2012-01-31',120.18,'S')
INSERT INTO #t4 VALUES (8,176,'2012-01-27',412.1,'P')
INSERT INTO #t4 VALUES (9,177,'2012-01-14',442.39,'P')
INSERT INTO #t4 VALUES (8,178,'2012-01-03',350.19,'P')
INSERT INTO #t4 VALUES (2,179,'2012-01-15',320,'P')
INSERT INTO #t4 VALUES (7,180,'2012-01-09',425.41,'P')
INSERT INTO #t4 VALUES (6,181,'2012-01-08',466.04,'S')
INSERT INTO #t4 VALUES (3,182,'2012-01-11',100.41,'P')
INSERT INTO #t4 VALUES (5,183,'2012-01-13',260.95,'S')
INSERT INTO #t4 VALUES (8,184,'2012-01-05',74.4,'S')
INSERT INTO #t4 VALUES (7,185,'2012-01-15',14.07,'P')
INSERT INTO #t4 VALUES (7,186,'2012-01-22',340.1,'S')
INSERT INTO #t4 VALUES (7,187,'2012-01-21',216.19,'S')
INSERT INTO #t4 VALUES (5,188,'2012-01-11',201.37,'P')
INSERT INTO #t4 VALUES (1,189,'2012-01-29',73.77,'S')
INSERT INTO #t4 VALUES (5,190,'2012-01-26',133.93,'S')
INSERT INTO #t4 VALUES (3,191,'2012-01-03',495.33,'S')
INSERT INTO #t4 VALUES (2,192,'2012-01-18',425.13,'S')
INSERT INTO #t4 VALUES (8,193,'2012-01-30',396.04,'S')
INSERT INTO #t4 VALUES (5,194,'2012-01-11',148.99,'P')
INSERT INTO #t4 VALUES (7,195,'2012-01-31',70.03,'P')
INSERT INTO #t4 VALUES (9,196,'2012-01-30',205.96,'P')
INSERT INTO #t4 VALUES (3,197,'2012-01-25',492.73,'S')
INSERT INTO #t4 VALUES (3,198,'2012-01-22',483.12,'S')
INSERT INTO #t4 VALUES (10,199,'2012-01-26',274.27,'S')
INSERT INTO #t4 VALUES (3,200,'2012-01-10',343.14,'P')
/*A market value table*/
CREATE TABLE #mv(
Account float,
trandate datetime,
emv money
)
/*Insert Rows into the market value table*/
INSERT INTO #mv VALUES (1,'2011-12-31',15643.93)
INSERT INTO #mv VALUES (1,'2012-01-05',15712.93)
INSERT INTO #mv VALUES (1,'2012-01-07',15436.12)
INSERT INTO #mv VALUES (1,'2012-01-08',15020.11)
INSERT INTO #mv VALUES (1,'2012-01-09',15359.63)
INSERT INTO #mv VALUES (1,'2012-01-11',15608.22)
INSERT INTO #mv VALUES (1,'2012-01-12',15196.35)
INSERT INTO #mv VALUES (1,'2012-01-13',15265.86)
INSERT INTO #mv VALUES (1,'2012-01-14',15324.42)
INSERT INTO #mv VALUES (1,'2012-01-15',15234.79)
INSERT INTO #mv VALUES (1,'2012-01-16',15345.13)
INSERT INTO #mv VALUES (1,'2012-01-18',15219.45)
INSERT INTO #mv VALUES (1,'2012-01-21',15789.64)
INSERT INTO #mv VALUES (1,'2012-01-23',15827.54)
INSERT INTO #mv VALUES (1,'2012-01-24',16140.84)
INSERT INTO #mv VALUES (1,'2012-01-27',16080.08)
INSERT INTO #mv VALUES (1,'2012-01-28',16050.71)
INSERT INTO #mv VALUES (1,'2012-01-29',15292.18)
INSERT INTO #mv VALUES (1,'2012-01-30',15037.89)
INSERT INTO #mv VALUES (1,'2012-01-31',15172.65)
INSERT INTO #mv VALUES (2,'2011-12-31',14886.95)
INSERT INTO #mv VALUES (2,'2012-01-03',14684.51)
INSERT INTO #mv VALUES (2,'2012-01-05',14819.27)
INSERT INTO #mv VALUES (2,'2012-01-06',14374.05)
INSERT INTO #mv VALUES (2,'2012-01-07',14664.25)
INSERT INTO #mv VALUES (2,'2012-01-08',14911.39)
INSERT INTO #mv VALUES (2,'2012-01-11',14801.46)
INSERT INTO #mv VALUES (2,'2012-01-12',14999.48)
INSERT INTO #mv VALUES (2,'2012-01-15',15369.01)
INSERT INTO #mv VALUES (2,'2012-01-16',16035.66)
INSERT INTO #mv VALUES (2,'2012-01-18',15166.81)
INSERT INTO #mv VALUES (2,'2012-01-19',15189)
INSERT INTO #mv VALUES (2,'2012-01-21',15341.51)
INSERT INTO #mv VALUES (2,'2012-01-22',16161.51)
INSERT INTO #mv VALUES (2,'2012-01-23',16336.81)
INSERT INTO #mv VALUES (2,'2012-01-24',16398.69)
INSERT INTO #mv VALUES (2,'2012-01-25',16815.77)
INSERT INTO #mv VALUES (2,'2012-01-27',16902.22)
INSERT INTO #mv VALUES (2,'2012-01-31',16848.31)
INSERT INTO #mv VALUES (3,'2011-12-31',14754.84)
INSERT INTO #mv VALUES (3,'2012-01-03',14369.97)
INSERT INTO #mv VALUES (3,'2012-01-04',14991.02)
INSERT INTO #mv VALUES (3,'2012-01-06',15014.21)
INSERT INTO #mv VALUES (3,'2012-01-09',14867.73)
INSERT INTO #mv VALUES (3,'2012-01-10',15308.52)
INSERT INTO #mv VALUES (3,'2012-01-11',15539.35)
INSERT INTO #mv VALUES (3,'2012-01-12',15749.06)
INSERT INTO #mv VALUES (3,'2012-01-15',15799.77)
INSERT INTO #mv VALUES (3,'2012-01-17',15990.04)
INSERT INTO #mv VALUES (3,'2012-01-21',16746.61)
INSERT INTO #mv VALUES (3,'2012-01-22',15993.81)
INSERT INTO #mv VALUES (3,'2012-01-23',16054.78)
INSERT INTO #mv VALUES (3,'2012-01-24',16027.69)
INSERT INTO #mv VALUES (3,'2012-01-25',15865.42)
INSERT INTO #mv VALUES (3,'2012-01-27',15535.24)
INSERT INTO #mv VALUES (3,'2012-01-29',15858.26)
INSERT INTO #mv VALUES (3,'2012-01-31',16234.09)
INSERT INTO #mv VALUES (4,'2011-12-31',14883.52)
INSERT INTO #mv VALUES (4,'2012-01-03',14507.26)
INSERT INTO #mv VALUES (4,'2012-01-04',14871.47)
INSERT INTO #mv VALUES (4,'2012-01-07',15279.96)
INSERT INTO #mv VALUES (4,'2012-01-08',15347.63)
INSERT INTO #mv VALUES (4,'2012-01-10',15369.4)
INSERT INTO #mv VALUES (4,'2012-01-13',15489.99)
INSERT INTO #mv VALUES (4,'2012-01-15',16004.41)
INSERT INTO #mv VALUES (4,'2012-01-22',16284.76)
INSERT INTO #mv VALUES (4,'2012-01-23',16370.82)
INSERT INTO #mv VALUES (4,'2012-01-25',16810.76)
INSERT INTO #mv VALUES (4,'2012-01-27',16930.8)
INSERT INTO #mv VALUES (4,'2012-01-29',17237.65)
INSERT INTO #mv VALUES (4,'2012-01-31',17335.56)
INSERT INTO #mv VALUES (5,'2011-12-31',14610.35)
INSERT INTO #mv VALUES (5,'2012-01-04',15040.47)
INSERT INTO #mv VALUES (5,'2012-01-07',15349.56)
INSERT INTO #mv VALUES (5,'2012-01-11',16108.95)
INSERT INTO #mv VALUES (5,'2012-01-13',15648.46)
INSERT INTO #mv VALUES (5,'2012-01-16',16267.15)
INSERT INTO #mv VALUES (5,'2012-01-20',17035.03)
INSERT INTO #mv VALUES (5,'2012-01-23',16650.17)
INSERT INTO #mv VALUES (5,'2012-01-25',16256.93)
INSERT INTO #mv VALUES (5,'2012-01-26',16688.99)
INSERT INTO #mv VALUES (5,'2012-01-28',16639.72)
INSERT INTO #mv VALUES (5,'2012-01-31',16710.02)
INSERT INTO #mv VALUES (6,'2011-12-31',14587.05)
INSERT INTO #mv VALUES (6,'2012-01-05',15293.13)
INSERT INTO #mv VALUES (6,'2012-01-08',14966.98)
INSERT INTO #mv VALUES (6,'2012-01-14',15156.74)
INSERT INTO #mv VALUES (6,'2012-01-15',16182.28)
INSERT INTO #mv VALUES (6,'2012-01-16',16318.18)
INSERT INTO #mv VALUES (6,'2012-01-17',16741.96)
INSERT INTO #mv VALUES (6,'2012-01-19',17283.04)
INSERT INTO #mv VALUES (6,'2012-01-21',17362.69)
INSERT INTO #mv VALUES (6,'2012-01-23',17658.53)
INSERT INTO #mv VALUES (6,'2012-01-24',18187.72)
INSERT INTO #mv VALUES (6,'2012-01-26',18079.52)
INSERT INTO #mv VALUES (6,'2012-01-28',18270.48)
INSERT INTO #mv VALUES (6,'2012-01-31',18338.34)
INSERT INTO #mv VALUES (7,'2011-12-31',15493.07)
INSERT INTO #mv VALUES (7,'2012-01-03',15589.04)
INSERT INTO #mv VALUES (7,'2012-01-04',15766.93)
INSERT INTO #mv VALUES (7,'2012-01-05',16199.73)
INSERT INTO #mv VALUES (7,'2012-01-09',16765.12)
INSERT INTO #mv VALUES (7,'2012-01-11',17267.5)
INSERT INTO #mv VALUES (7,'2012-01-12',17421.47)
INSERT INTO #mv VALUES (7,'2012-01-14',17539.65)
INSERT INTO #mv VALUES (7,'2012-01-15',17556.64)
INSERT INTO #mv VALUES (7,'2012-01-16',17366.72)
INSERT INTO #mv VALUES (7,'2012-01-17',17036.57)
INSERT INTO #mv VALUES (7,'2012-01-20',17317.93)
INSERT INTO #mv VALUES (7,'2012-01-21',17229.89)
INSERT INTO #mv VALUES (7,'2012-01-22',16981.2)
INSERT INTO #mv VALUES (7,'2012-01-23',17440.47)
INSERT INTO #mv VALUES (7,'2012-01-25',17032.69)
INSERT INTO #mv VALUES (7,'2012-01-26',17284.51)
INSERT INTO #mv VALUES (7,'2012-01-27',17772.77)
INSERT INTO #mv VALUES (7,'2012-01-28',18166.43)
INSERT INTO #mv VALUES (7,'2012-01-31',18342.46)
INSERT INTO #mv VALUES (8,'2011-12-31',15722.74)
INSERT INTO #mv VALUES (8,'2012-01-03',16099.04)
INSERT INTO #mv VALUES (8,'2012-01-05',16082.3)
INSERT INTO #mv VALUES (8,'2012-01-06',16534.99)
INSERT INTO #mv VALUES (8,'2012-01-11',16725.38)
INSERT INTO #mv VALUES (8,'2012-01-12',16760.04)
INSERT INTO #mv VALUES (8,'2012-01-13',16432.87)
INSERT INTO #mv VALUES (8,'2012-01-14',16902.11)
INSERT INTO #mv VALUES (8,'2012-01-20',16763.25)
INSERT INTO #mv VALUES (8,'2012-01-21',17031.86)
INSERT INTO #mv VALUES (8,'2012-01-24',16955.53)
INSERT INTO #mv VALUES (8,'2012-01-26',16833.21)
INSERT INTO #mv VALUES (8,'2012-01-27',17307.71)
INSERT INTO #mv VALUES (8,'2012-01-29',17858.09)
INSERT INTO #mv VALUES (8,'2012-01-30',17618.78)
INSERT INTO #mv VALUES (8,'2012-01-31',17716.41)
INSERT INTO #mv VALUES (9,'2011-12-31',15314.82)
INSERT INTO #mv VALUES (9,'2012-01-03',15789.62)
INSERT INTO #mv VALUES (9,'2012-01-04',15874.77)
INSERT INTO #mv VALUES (9,'2012-01-05',16257.35)
INSERT INTO #mv VALUES (9,'2012-01-06',15910.62)
INSERT INTO #mv VALUES (9,'2012-01-08',16054.06)
INSERT INTO #mv VALUES (9,'2012-01-09',15967.82)
INSERT INTO #mv VALUES (9,'2012-01-14',16561.38)
INSERT INTO #mv VALUES (9,'2012-01-15',17051.1)
INSERT INTO #mv VALUES (9,'2012-01-16',16983.01)
INSERT INTO #mv VALUES (9,'2012-01-21',17603.76)
INSERT INTO #mv VALUES (9,'2012-01-24',17435.68)
INSERT INTO #mv VALUES (9,'2012-01-25',17159.46)
INSERT INTO #mv VALUES (9,'2012-01-28',17680.55)
INSERT INTO #mv VALUES (9,'2012-01-29',17279.74)
INSERT INTO #mv VALUES (9,'2012-01-30',17538.1)
INSERT INTO #mv VALUES (9,'2012-01-31',17662.49)
INSERT INTO #mv VALUES (10,'2011-12-31',15025.36)
INSERT INTO #mv VALUES (10,'2012-01-03',14570.57)
INSERT INTO #mv VALUES (10,'2012-01-06',14627.86)
INSERT INTO #mv VALUES (10,'2012-01-08',14826.02)
INSERT INTO #mv VALUES (10,'2012-01-10',15180.7)
INSERT INTO #mv VALUES (10,'2012-01-11',15353.16)
INSERT INTO #mv VALUES (10,'2012-01-13',15099.81)
INSERT INTO #mv VALUES (10,'2012-01-14',15636.38)
INSERT INTO #mv VALUES (10,'2012-01-17',15439.98)
INSERT INTO #mv VALUES (10,'2012-01-18',15586.85)
INSERT INTO #mv VALUES (10,'2012-01-20',15849.07)
INSERT INTO #mv VALUES (10,'2012-01-21',15548.2)
INSERT INTO #mv VALUES (10,'2012-01-22',15463.33)
INSERT INTO #mv VALUES (10,'2012-01-24',15696.9)
INSERT INTO #mv VALUES (10,'2012-01-26',15872.72)
INSERT INTO #mv VALUES (10,'2012-01-31',15884.25)
/*Calculate the time-weighted rate of return*/
SELECT Account
,wct.TWRR(c,d,mv) as TWRR
FROM (
SELECT Account
,Trandate
,CASE PS
WHEN 'P' THEN TranAmt
ELSE -TranAmt
END
,'False'
FROM #t4
UNION ALL
SELECT Account
,Trandate
,-emv
,'True'
FROM #mv
) n(account, d, c, mv)
GROUP BY Account
This produces the following result.
Account TWRR
---------------------- ----------------------
1 0.0975186976118223
2 0.0770033966232755
3 0.08839331492087
4 0.0693993905057961
5 0.0664506003772058
6 0.0813486278503901
7 0.0866470429823187
8 0.056839427082984
9 0.0836794835923629
10 0.0631681752863544
See Also