TWRR
Updated 2024-02-29 20:53:53.847000
Syntax
SELECT [westclintech].[wct].[TWRR] (
<@CF, float,>
,<@CFDate, datetime),>)
,<@MV, bit),>)
Description
Use the aggregate function 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:
r=\prod_0^n\frac{MV_n+S_n}{MV_{n-1}+P_n}-1
Wherer is the time the time weighted rate of returnn is the period for which the return is calculatedMV is the ending market value for the periodP is the amount that has been added to the portfolioS is the amount that has been subtracted from the portfolio
Arguments
@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.
@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.
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 an 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.
{"columns":[{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"BMV Pur"},{"field":"hases Sa","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"es","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EMV"}],"rows":[{"Date":"2012-01-03","BMV Pur":"15,000.00","hases Sa":"436.49","es":"0.00","EMV":"15,477.26"},{"Date":"2012-01-10","BMV Pur":"15,477.26","hases Sa":"0.00","es":"-495.97","EMV":"15,115.74"},{"Date":"2012-01-17","BMV Pur":"15,115.74","hases Sa":"297.92","es":"-609.07","EMV":"14,878.10"},{"Date":"2012-01-24","BMV Pur":"14,878.10","hases Sa":"157.06","es":"-880.28","EMV":"14,197.25"},{"Date":"2012-01-31","BMV Pur":"14,197.25","hases Sa":"0.00","es":"0.00","EMV":"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 directly 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.
{"columns":[{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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 performance 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.
{"columns":[{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Purchases","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EMV"}],"rows":[{"Date":"2011-12-31","Purchases":"0.00","Sales":"0.00","EMV":"15,000.00"},{"Date":"2012-01-03","Purchases":"436.49","Sales":"0.00","EMV":"15,477.23"},{"Date":"2012-01-10","Purchases":"0.00","Sales":"-495.97","EMV":"15,115.74"},{"Date":"2012-01-17","Purchases":"297.92","Sales":"-609-07","EMV":"14,878.10"},{"Date":"2012-01-24","Purchases":"157.06","Sales":"-880.28","EMV":"14,197.25"},{"Date":"2012-01-31","Purchases":"0.00","Sales":"0.00","EMV":"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.
{"columns":[{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"TWRR":"0.0246243629116802"}]}
Given the following information, calculate the time –weighted rate of return for the portfolio for the first quarter.
{"columns":[{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Market Value"},{"field":"Cash Flow"},{"field":"Market Value Post Cash Flow"}],"rows":[{"Date":"2012-Dec-31","Market Value":"500,000"},{"Date":"2013-Jan-31","Market Value":"509,000"},{"Date":"2013-Feb-19","Market Value":"513,000","Cash Flow":"+50,000","Market Value Post Cash Flow":"563,000"},{"Date":"2013-Feb-28","Market Value":"575,000"},{"Date":"2013-Mar-12","Market Value":"585,000","Cash Flow":"-20,000","Market Value Post Cash Flow":"575,000"},{"Date":"2013-Mar-31","Market Value":"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.
{"columns":[{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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)
);
GO
/*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');
GO
/*A market value table*/
CREATE TABLE #mv
(
Account float,
trandate datetime,
emv money
);
GO
/*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.
{"columns":[{"field":"Account","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Account":"1","TWRR":"0.0975186976118223"},{"Account":"2","TWRR":"0.0770033966232755"},{"Account":"3","TWRR":"0.08839331492087"},{"Account":"4","TWRR":"0.0693993905057961"},{"Account":"5","TWRR":"0.0664506003772058"},{"Account":"6","TWRR":"0.0813486278503901"},{"Account":"7","TWRR":"0.0866470429823187"},{"Account":"8","TWRR":"0.056839427082984"},{"Account":"9","TWRR":"0.0836794835923629"},{"Account":"10","TWRR":"0.0631681752863544"}]}
See Also
EMDIETZ - Enhanced Modified Dietz
GTWRR - Generalized time-weighted rate of return
LMDIETZ - Linked Modified Dietz
TWROR - Time-weighted rate of return with market value indicators