March 22, 2019 at 12:52 pm
Lynn Pettis - Friday, March 22, 2019 12:48 PMRacerX330 - Friday, March 22, 2019 12:39 PMLynn Pettis - Friday, March 22, 2019 11:00 AMRacerX330 - Friday, March 22, 2019 10:53 AMdrew.allen - Friday, March 22, 2019 10:35 AMRacerX330 - Friday, March 22, 2019 9:50 AMfair enough. the table-valued function returns investment returns for each portfolio for the specified time period. I want to run a standard set of date ranges that grows as time advances so I don't have to keep doing it manually.additionally, a second table-valued function returns investment returns on benchmark indices and I also want to run it through the same date combination table and calculate a running difference between the account level returns and the benchmark returns instead of having to do it manually.
let me know if you need more information
You most definitely do not need a loop to calculate this. I did this same exact thing last month. You want to use the UVR when you calculate this and you will need to convert this to a LOG(), do a running sum, and then convert back to a POWER(). You can easily convert back and forth from an incremental return to a UVR if you are starting and/or ending with an incremental return instead of a UVR.
Drew
Don't know what you mean when you use the term UVR.
You're right, since we don't have a Product function, the sums of the logs have been used to determine the a period return (return using date constraints) using time-weighted daily returns, and then converted back after the SUM using EXP.
What I'm after is slightly different, the return calculations are correct and verified, and the table-valued function returns them based on the start_date and end_date provided, so I don't want to change how the underlying returns are calculated. I want to look at them on a monthly basis based on different overall starting and ending points. e.g. monthly returns and cumulative returns by month. I've created a table that contains the start_date, end_date period combinations that I want to measure, the table literally has two columns start_date and end_date and nothing else. Since I already have the table-valued function giving me the returns, I want the inputs to the table-valued function to be this table with the start date and end date period combinations.
Again, how are we supposed to help you? You have told us what you want but have failed to provide the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT statements) with which to work, expected results based on the sample data, the DDL for the function(s) that we would need to use in a query to develop to help answer your question.
We cannot see what you see since we don't have access, nor would we want access, to your systems to try and help you. That takes me back to the link to article I posted for you to read and follow.
You need a create table statement for a table that has two date columns? I have no INSERT statements, there are no INSERT statements, I think you didn't read my post, and I can't give you what I don't have. I can't give you sample data, our data is private. I have no idea what DDL. I think i'll just look for a solution elsewhere, since I guess my problem doesn't fit within the narrow set of parameters within which you are able to function.
And you missed the part about sample data, meaning data that is representative of the problem domain. I am out of here if you can't help us help you. I am not going to take the time or effort to do that which you should do since you are the one asking for help.
probably for the best, the attempt to assist is appreciated tho.
March 22, 2019 at 1:06 pm
drew.allen - Friday, March 22, 2019 12:37 PMRacerX330 - Friday, March 22, 2019 10:53 AMdrew.allen - Friday, March 22, 2019 10:35 AMRacerX330 - Friday, March 22, 2019 9:50 AMfair enough. the table-valued function returns investment returns for each portfolio for the specified time period. I want to run a standard set of date ranges that grows as time advances so I don't have to keep doing it manually.additionally, a second table-valued function returns investment returns on benchmark indices and I also want to run it through the same date combination table and calculate a running difference between the account level returns and the benchmark returns instead of having to do it manually.
let me know if you need more information
You most definitely do not need a loop to calculate this. I did this same exact thing last month. You want to use the UVR when you calculate this and you will need to convert this to a LOG(), do a running sum, and then convert back to a POWER(). You can easily convert back and forth from an incremental return to a UVR if you are starting and/or ending with an incremental return instead of a UVR.
Drew
Don't know what you mean when you use the term UVR.
You're right, since we don't have a Product function, the sums of the logs have been used to determine the a period return (return using date constraints) using time-weighted daily returns, and then converted back after the SUM using EXP.
What I'm after is slightly different, the return calculations are correct and verified, and the table-valued function returns them based on the start_date and end_date provided, so I don't want to change how the underlying returns are calculated. I want to look at them on a monthly basis based on different overall starting and ending points. e.g. monthly returns and cumulative returns by month. I've created a table that contains the start_date, end_date period combinations that I want to measure, the table literally has two columns start_date and end_date and nothing else. Since I already have the table-valued function giving me the returns, I want the inputs to the table-valued function to be this table with the start date and end date period combinations.
You mean like in Example K?
Drew
possibly, i'll give it a shot, i've never used the APPLY operator before, that might be it... thanks
March 22, 2019 at 1:28 pm
patrickmcginnis59 10839 - Friday, March 22, 2019 11:41 AMRacerX330 - Friday, March 22, 2019 9:29 AMHi all, I have a question about the best/most efficient way to accomplish what I'm trying to do.I have a table-valued function that takes two inputs a start date and end date. func_get_stuff(start_end, end_date), the output of the table-valued function has the columns: start_end, end_date, id_stuff, value_calculated
I have a table with start date and end date combinations.
I want to run a stored procedure (if that's the right way to do it) or a query that will loop through each combination in the table and output the result
thanks in advance.
Really works best here when you supply some data and metadata, that way folks can plug that into a test server and supply some working sql.In any case, here's a good page about table valued functions, and then you can use them with a "cross apply" to avoid loops:
Putting one together then using it with 'CROSS APPLY'-- CREATE A TRIVIAL TEST FUNCTION THAT FINDS THE NUMBER OF DAYS BETWEEN DATES
CREATE FUNCTION testfunc (@start_date datetime, @end_date datetime)
RETURNS TABLE
AS
RETURN SELECT @start_date [start_date], @end_date [end_date], datediff(dd, @start_date, @end_date) days_betweenGO
-- PRETEND 'TESTTAB' IS REALLY A TABLE OF START AND END DATES
WITH TESTTAB (START1, END1)
AS
(
SELECT '2019-01-01' START1, '2019-02-01' END1
UNION
SELECT '2019-02-01' START1, '2019-03-01' END1
)-- SELECT STATEMENT WITH CROSS APPLY THAT USES A TABLE VALUED FUNCTION
SELECT [start_date], [end_date], [days_between] FROM TESTTAB
CROSS APPLY dbo.testfunc (START1, END1)-- LOL COULDN'T GET TAGS TO WORK TODAY
@patrickmcginnis59
the cross apply worked like magic, thanks!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply