March 22, 2019 at 9:29 am
Hi 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.
March 22, 2019 at 9:45 am
That's entirely too vague to give you any meaningful answer. Generally it is best to use a set-based approach where possible, but you haven't given us any information about how you are using your data, so we can't even determine if a set-based approach is possible.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2019 at 9:50 am
fair 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
March 22, 2019 at 10:13 am
RacerX330 - 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2019 at 10:27 am
Lynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks for the reply. Not sure what you're getting at however, I'm not posting any data.
Since I'm not a SQL Server database theorist or advanced super-user, I don't really know exactly what the options are or what is needed to answer the question. I've already provided some explanation on how the data is being used, and generally how it is structured.
I just want to use a table as an input to a table-valued function, and not being a T-SQL expert, I'd like someone to walk me through some options on how that is done. What else, specifically is needed for that?
March 22, 2019 at 10:35 am
RacerX330 - 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2019 at 10:36 am
RacerX330 - Friday, March 22, 2019 10:27 AMLynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks for the reply. Not sure what you're getting at however, I'm not posting any data.
Since I'm not a SQL Server database theorist or advanced super-user, I don't really know exactly what the options are or what is needed to answer the question. I've already provided some explanation on how the data is being used, and generally how it is structured.
I just want to use a table as an input to a table-valued function, and not being a T-SQL expert, I'd like someone to walk me through some options on how that is done. What else, specifically is needed for that?
So, how are we supposed to help you when we have nothing with which to work? We are volunteers offering to help by giving of our time. This means you have to help us help you.
March 22, 2019 at 10:53 am
drew.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.
March 22, 2019 at 10:56 am
Lynn Pettis - Friday, March 22, 2019 10:36 AMRacerX330 - Friday, March 22, 2019 10:27 AMLynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis - Friday, March 22, 2019 10:13 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
Sure, read the following and then post what we need to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks for the reply. Not sure what you're getting at however, I'm not posting any data.
Since I'm not a SQL Server database theorist or advanced super-user, I don't really know exactly what the options are or what is needed to answer the question. I've already provided some explanation on how the data is being used, and generally how it is structured.
I just want to use a table as an input to a table-valued function, and not being a T-SQL expert, I'd like someone to walk me through some options on how that is done. What else, specifically is needed for that?
So, how are we supposed to help you when we have nothing with which to work? We are volunteers offering to help by giving of our time. This means you have to help us help you.
We're not communicating effectively. You keep asking me for "something" without defining what the "something" is that you want, and I keep asking you what the "something" is and you keep asking me to give you the "something".
March 22, 2019 at 11:00 am
RacerX330 - 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.
March 22, 2019 at 11:41 am
RacerX330 - 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.
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:
-- 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_between
-- LOL COULDN'T GET TAGS TO WORK TODAY
March 22, 2019 at 12:37 pm
RacerX330 - 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2019 at 12:39 pm
Lynn 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.
March 22, 2019 at 12:41 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
this is very helpful thank you.
March 22, 2019 at 12:48 pm
RacerX330 - 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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply