How to change query timeout?

I'm reporting from a web service that takes so long to return that Reporting
Services times out... how can I increase the timeout value when querying for
data?
TIA - ekkisDont try to increase the timeout, instead optimize your query or use stored
proc.
Amarnath
"ekkis" wrote:
> I'm reporting from a web service that takes so long to return that Reporting
> Services times out... how can I increase the timeout value when querying for
> data?
> TIA - ekkis|||perhaps I didn't explain myself correctly. I am reporting from a web service
so there are no stored procedures involved and the "query" is simply the
request to fetch data from the web service.
I have no control over the foreign server so I need to increase the timeout
value. How can I do that?

How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

How to capture result from READTEXT?

In the continuing saga of dealing with blobs . . .
READTEXT is a fine function, but useless by itself. I need to return the
results of READTEXT into a varchar column but have been unable to do so or
even find a similar example. Displaying the results in Query Analyzer is all
the example MS provides.
I need something like the following:
Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
@.chunkindex @.chunksize
Any suggestions?
MichaelPlease don't start new threads, there is already a thread about this you
started half an hour ago.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column but have been unable to do so or
> even find a similar example. Displaying the results in Query Analyzer is
> all
> the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael|||> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column
I'm not sure I understand this. What exactly are you trying to accomplish
(e.g. forget about READTEXT for a minute, what are you going to actually DO
with each of these chunks)?|||Snake wrote:
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return
> the results of READTEXT into a varchar column but have been unable to
> do so or even find a similar example. Displaying the results in Query
> Analyzer is all the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael
How about querying the column directly using a SELECT statement?
Declare @.MyText VARCHAR(1000)
Select
@.MyText = SUBSTRING(pr_info, 1, 100)
From
pubs.dbo.pub_info
Where
pub_id = '0736'
Select @.MyText
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Aaron,
If you really read my orignal posting you will see that while these postings
are related, this one is more specific and focused on how to use the
READTEXT. Since your own responses to each of my postings is different, at
least on some level even you see them differently.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Please don't start new threads, there is already a thread about this you
> started half an hour ago.
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
>
>|||David,
Thanks for your response. I believe I read in BOL that for ntext columns
over 4000 characters one must use the READTEXT function. My data is longer
than 4000, therefore I must use READTEXT to parse my data and return it to m
y
program in, say, 1k chunks. Is this an incorrect approach?
I am warry of the pubs examples and sample code because they always assume
the most simplistic situations and from my point-of-view ignore much of the
real-world. One must always read the associated text in BOL to find the
gotchas!
Have a very nice 3-day wend.
"David Gugick" wrote:

> Snake wrote:
> How about querying the column directly using a SELECT statement?
> Declare @.MyText VARCHAR(1000)
> Select
> @.MyText = SUBSTRING(pr_info, 1, 100)
> From
> pubs.dbo.pub_info
> Where
> pub_id = '0736'
> Select @.MyText
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Actually, my reply here was meant for the original thread, and applies
equally to both. They are still both dealing with how to get a chunk of
text from readtext into a varchar variable. There is no reason to carry on
multiple distinct conversations about the same thing.

> Aaron,
> If you really read my orignal posting you will see that while these
> postings
> are related, this one is more specific and focused on how to use the
> READTEXT. Since your own responses to each of my postings is different, at
> least on some level even you see them differently.|||> Thanks for your response. I believe I read in BOL that for ntext columns
> over 4000 characters one must use the READTEXT function. My data is
> longer
> than 4000, therefore I must use READTEXT to parse my data and return it to
> my
> program in, say, 1k chunks. Is this an incorrect approach?
Well, did you TRY using SUBSTRING? Did you try the code samples that have
been posted?
Also, why would you want to use 1k chunks? Didn't you say you wanted to
stuff it into an NVARCHAR variable or column? If so, then why not use 4k
chunks? And if all you are doing is returning the data to your program, why
not just say SELECT NTextColumn FROM table, the program shouldn't have
display limitations like Query Analyzer (8,192 characters).
A

How to call stored procedure to table?

Hi....

I have problem and I need your help

I stored a procedure in the Projects Folder in my computer

and I want to return the procedure result in a column inside table

How I can do that???

thank you

Please any one can help me??????????

|||

Hi,

From your description, you have mentioned that you want to return the procedure result in a column.

Well, do you mean you want to select a column from your database with several rows, and then get these rows from your stored procedure, right?

If so, you can execute a procedure that return rows. To execute a stored procedure that returns row, you can run a TableAdapter query that is configured to run a stored procedure (for example, CustomersTableAdapter.Fill(CustomersDataTable)).

If your application does not use TableAdapters, call the ExecuteReader method on a command object, setting its CommandType property to StoredProcedure. ("Command object" refers to the specific command for the .NET Framework Data Provider that your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)

For more information, see
http://msdn2.microsoft.com/en-us/library/d7125bke(VS.80).aspx

Thanks.

How to calculate cumulative rolling product on a 5 day window.

Here's what the data looks like:
REC# RETURN DESIRED RESULT
-- -- --
1 1.000859
2 0.999705
3 1.000471
4 0.994672
5 1.001945 -0.24%
6 0.999718 -0.35%
7 0.997262 -0.59%
8 0.997613 -0.88%
9 1.000932 -0.25%
10 0.999584 -0.49%
To calculate the DESIRED RESULT on REC# 5, the following calculation
is needed: (notice 5 days of returns are multiplied together)
((1.000859 * 0.999705 * 1.000471 * 0.994672 * 1.001945) - 1) * 100 = -0.24
In order to calculate the DESIRED RESULT for REC# 6 I use the same
calculation only using RETURN values from REC#'s 2 through 6.
DESIRED RESULT on REC# 7 uses RETURN values from REC#'s 3 through 7
It has to be a 5 day window of values that is why REC#'s 1-4 have no
DESIRED RESULT.
I'm trying to figure out how to do this without using a cursor....I
hate cursors.
Any ideas I've been thinking about if for a couple hours now and
haven't found a good solution yet.On Wed, 19 Sep 2007 19:45:46 -0000, Izzy wrote:
>Here's what the data looks like:
>REC# RETURN DESIRED RESULT
>-- -- --
> 1 1.000859
> 2 0.999705
> 3 1.000471
> 4 0.994672
> 5 1.001945 -0.24%
> 6 0.999718 -0.35%
> 7 0.997262 -0.59%
> 8 0.997613 -0.88%
> 9 1.000932 -0.25%
> 10 0.999584 -0.49%
>
>To calculate the DESIRED RESULT on REC# 5, the following calculation
>is needed: (notice 5 days of returns are multiplied together)
>((1.000859 * 0.999705 * 1.000471 * 0.994672 * 1.001945) - 1) * 100 =>-0.24
>
>In order to calculate the DESIRED RESULT for REC# 6 I use the same
>calculation only using RETURN values from REC#'s 2 through 6.
>
>DESIRED RESULT on REC# 7 uses RETURN values from REC#'s 3 through 7
>
>It has to be a 5 day window of values that is why REC#'s 1-4 have no
>DESIRED RESULT.
>
>I'm trying to figure out how to do this without using a cursor....I
>hate cursors.
>
>Any ideas I've been thinking about if for a couple hours now and
>haven't found a good solution yet.
Hi Izzy,
If the REC# column is guaranteed to hold consecutive numbers (i.e. no
gaps), you can use
SELECT a.RecNo, a."Return",
CASE
WHEN COUNT(b."Return") = 5
THEN (POWER(10.0, SUM(LOG10(b."Return"))) - 1) * 100
ELSE NULL
END AS Result
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.RecNo BETWEEN a.RecNo - 4 AND a.RecNo
GROUP BY a.RecNo, a."Return";
Note that I'm using POWER(10.0,SUM(LOG10(Value))) to mimic the aggregate
product function that SQL Server lacks. This technique works if all
values are >0, as in your sample. If values =0 or <0 are allwoed as
well, you'll have to use a more complicated expression to get the
aggregate product (Google for SQL Server product aggregate Itzik Ben-Gan
and you'll find it).
If RecNo values can have gaps, *and* you are on SQL Server 2005, you can
generate consecutive rownumbers by using ROW_NUMBER(). If you're still
on SQL Server 2000, you'll need some real nasty work - if you want me to
help with that, you'll have to post CREATE TABLE and SELECT statements
so that I can set up a test environment by using copy and paste.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||I suggest you read the thread:
microsoft.public.sqlserver.programming
Sunday, July 01, 2007 7:45 AM
TSQl-Accmulations
http://tinyurl.com/yvdqbc
Look especially at these 2 solutions in the thread (which assume S2005):
Steve Dassin
Monday, July 02, 2007 10:18 PM
Itzik Ben-Gan
Tuesday, July 03, 2007 5:16 AM
(But I think Itzik Ben-Gan also shows possible S2000 solutions).
In your case (given S2005) the row definition is:
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
ie, exec SqlWindowCustomers @.from=-4,@.to=0
You can use Hugo's product aggregate to get what you want.
Easy solutions to problems like this is what Itzik Ben-Gan argued for in:
Itzik Ben-Gan and Sujata Mehta
OVER Clause and Ordered Calculations
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
which follow the sql standard of an sql window that's quite simple and
powerful, especially for moving/running sum problems.
And here is the MS response regarding Katami (S2008):
OVER clause enhancement request - ORDER BY for aggregates
OVER clause enhancement request - ROWS and RANGE window sub-clauses
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392
'This feature unfortunately did not fit into our schedule for SQL Server
2008.'
If Itzik Ben-Gan has so little pull what does that say for the rest
of us! Just how do they decide on what features to implement?
Who the hell is in charge there. Is anyone in charge? :-)
In this case I think it's fair to say the Connect(ion) is broken :-)
For more on this subject see:
http://beyondsql.blogspot.com/2007/07/dataphor-example-of-rapid-application.html
best,
www.beyondsql.blogspot.com|||On Sep 19, 4:46 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 19 Sep 2007 19:45:46 -0000, Izzy wrote:
> >Here's what the data looks like:
> >REC# RETURN DESIRED RESULT
> >-- -- --
> > 1 1.000859
> > 2 0.999705
> > 3 1.000471
> > 4 0.994672
> > 5 1.001945 -0.24%
> > 6 0.999718 -0.35%
> > 7 0.997262 -0.59%
> > 8 0.997613 -0.88%
> > 9 1.000932 -0.25%
> > 10 0.999584 -0.49%
> >To calculate the DESIRED RESULT on REC# 5, the following calculation
> >is needed: (notice 5 days of returns are multiplied together)
> >((1.000859 * 0.999705 * 1.000471 * 0.994672 * 1.001945) - 1) * 100 => >-0.24
> >In order to calculate the DESIRED RESULT for REC# 6 I use the same
> >calculation only using RETURN values from REC#'s 2 through 6.
> >DESIRED RESULT on REC# 7 uses RETURN values from REC#'s 3 through 7
> >It has to be a 5 day window of values that is why REC#'s 1-4 have no
> >DESIRED RESULT.
> >I'm trying to figure out how to do this without using a cursor....I
> >hate cursors.
> >Any ideas I've been thinking about if for a couple hours now and
> >haven't found a good solution yet.
> Hi Izzy,
> If the REC# column is guaranteed to hold consecutive numbers (i.e. no
> gaps), you can use
> SELECT a.RecNo, a."Return",
> CASE
> WHEN COUNT(b."Return") = 5
> THEN (POWER(10.0, SUM(LOG10(b."Return"))) - 1) * 100
> ELSE NULL
> END AS Result
> FROM YourTable AS a
> INNER JOIN YourTable AS b
> ON b.RecNo BETWEEN a.RecNo - 4 AND a.RecNo
> GROUP BY a.RecNo, a."Return";
> Note that I'm using POWER(10.0,SUM(LOG10(Value))) to mimic the aggregate
> product function that SQL Server lacks. This technique works if all
> values are >0, as in your sample. If values =0 or <0 are allwoed as
> well, you'll have to use a more complicated expression to get the
> aggregate product (Google for SQL Server product aggregate Itzik Ben-Gan
> and you'll find it).
> If RecNo values can have gaps, *and* you are on SQL Server 2005, you can
> generate consecutive rownumbers by using ROW_NUMBER(). If you're still
> on SQL Server 2000, you'll need some real nasty work - if you want me to
> help with that, you'll have to post CREATE TABLE and SELECT statements
> so that I can set up a test environment by using copy and paste.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
Thanks Hugo I think we are on the right path......but
When I run your query it returns only 0.0 for all rows.
RecNo is consecutive without gaps.
Return column is of type FLOAT.
I'm using SQL Server 2005
To set up a test:
CREATE TABLE #HIST_RETURNS
(
DAYNUM INT,
TOTAL_RETURN FLOAT
);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (1, 1.000859);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (2, 0.999705);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (3, 1.000471);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (4, 0.994672);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (5, 1.001945);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (6, 0.999718);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (7, 0.997262);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (8, 0.997613);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (9, 1.000932);
INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
VALUES (10, 0.999584);
SELECT A.DAYNUM, A.TOTAL_RETURN,
CASE
WHEN COUNT(B.TOTAL_RETURN) = 5
THEN (POWER(10.0, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
ELSE NULL
END AS RESULT
FROM #HIST_RETURNS AS A
JOIN #HIST_RETURNS AS B ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND
A.DAYNUM
GROUP BY A.DAYNUM, A.TOTAL_RETURN
ORDER BY A.DAYNUM;
DROP TABLE #HIST_RETURNS;
Thanks for the response.|||On Sep 20, 8:05 am, Izzy <israel.rich...@.gmail.com> wrote:
> On Sep 19, 4:46 pm, Hugo Kornelis
>
>
> <h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > On Wed, 19 Sep 2007 19:45:46 -0000, Izzy wrote:
> > >Here's what the data looks like:
> > >REC# RETURN DESIRED RESULT
> > >-- -- --
> > > 1 1.000859
> > > 2 0.999705
> > > 3 1.000471
> > > 4 0.994672
> > > 5 1.001945 -0.24%
> > > 6 0.999718 -0.35%
> > > 7 0.997262 -0.59%
> > > 8 0.997613 -0.88%
> > > 9 1.000932 -0.25%
> > > 10 0.999584 -0.49%
> > >To calculate the DESIRED RESULT on REC# 5, the following calculation
> > >is needed: (notice 5 days of returns are multiplied together)
> > >((1.000859 * 0.999705 * 1.000471 * 0.994672 * 1.001945) - 1) * 100 => > >-0.24
> > >In order to calculate the DESIRED RESULT for REC# 6 I use the same
> > >calculation only using RETURN values from REC#'s 2 through 6.
> > >DESIRED RESULT on REC# 7 uses RETURN values from REC#'s 3 through 7
> > >It has to be a 5 day window of values that is why REC#'s 1-4 have no
> > >DESIRED RESULT.
> > >I'm trying to figure out how to do this without using a cursor....I
> > >hate cursors.
> > >Any ideas I've been thinking about if for a couple hours now and
> > >haven't found a good solution yet.
> > Hi Izzy,
> > If the REC# column is guaranteed to hold consecutive numbers (i.e. no
> > gaps), you can use
> > SELECT a.RecNo, a."Return",
> > CASE
> > WHEN COUNT(b."Return") = 5
> > THEN (POWER(10.0, SUM(LOG10(b."Return"))) - 1) * 100
> > ELSE NULL
> > END AS Result
> > FROM YourTable AS a
> > INNER JOIN YourTable AS b
> > ON b.RecNo BETWEEN a.RecNo - 4 AND a.RecNo
> > GROUP BY a.RecNo, a."Return";
> > Note that I'm using POWER(10.0,SUM(LOG10(Value))) to mimic the aggregate
> > product function that SQL Server lacks. This technique works if all
> > values are >0, as in your sample. If values =0 or <0 are allwoed as
> > well, you'll have to use a more complicated expression to get the
> > aggregate product (Google for SQL Server product aggregate Itzik Ben-Gan
> > and you'll find it).
> > If RecNo values can have gaps, *and* you are on SQL Server 2005, you can
> > generate consecutive rownumbers by using ROW_NUMBER(). If you're still
> > on SQL Server 2000, you'll need some real nasty work - if you want me to
> > help with that, you'll have to post CREATE TABLE and SELECT statements
> > so that I can set up a test environment by using copy and paste.
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis-Hide quoted text -
> > - Show quoted text -
> Thanks Hugo I think we are on the right path......but
> When I run your query it returns only 0.0 for all rows.
> RecNo is consecutive without gaps.
> Return column is of type FLOAT.
> I'm using SQL Server 2005
> To set up a test:
> CREATE TABLE #HIST_RETURNS
> (
> DAYNUM INT,
> TOTAL_RETURN FLOAT
> );
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (1, 1.000859);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (2, 0.999705);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (3, 1.000471);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (4, 0.994672);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (5, 1.001945);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (6, 0.999718);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (7, 0.997262);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (8, 0.997613);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (9, 1.000932);
> INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> VALUES (10, 0.999584);
> SELECT A.DAYNUM, A.TOTAL_RETURN,
> CASE
> WHEN COUNT(B.TOTAL_RETURN) = 5
> THEN (POWER(10.0, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
> ELSE NULL
> END AS RESULT
> FROM #HIST_RETURNS AS A
> JOIN #HIST_RETURNS AS B ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND
> A.DAYNUM
> GROUP BY A.DAYNUM, A.TOTAL_RETURN
> ORDER BY A.DAYNUM;
> DROP TABLE #HIST_RETURNS;
> Thanks for the response.- Hide quoted text -
> - Show quoted text -
I think I figured it out, by adding more zero's to the "10.0" and
making it "10.00000".
SELECT A.DAYNUM, A.TOTAL_RETURN,
CASE
WHEN COUNT(B.TOTAL_RETURN) = 5
THEN (POWER(10.000000, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
ELSE NULL
END AS RESULT
FROM #HIST_RETURNS AS A
JOIN #HIST_RETURNS AS B ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND
A.DAYNUM
GROUP BY A.DAYNUM, A.TOTAL_RETURN
ORDER BY A.DAYNUM;|||On Sep 20, 8:17 am, Izzy <israel.rich...@.gmail.com> wrote:
> On Sep 20, 8:05 am, Izzy <israel.rich...@.gmail.com> wrote:
>
>
> > On Sep 19, 4:46 pm, Hugo Kornelis
> > <h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > > On Wed, 19 Sep 2007 19:45:46 -0000, Izzy wrote:
> > > >Here's what the data looks like:
> > > >REC# RETURN DESIRED RESULT
> > > >-- -- --
> > > > 1 1.000859
> > > > 2 0.999705
> > > > 3 1.000471
> > > > 4 0.994672
> > > > 5 1.001945 -0.24%
> > > > 6 0.999718 -0.35%
> > > > 7 0.997262 -0.59%
> > > > 8 0.997613 -0.88%
> > > > 9 1.000932 -0.25%
> > > > 10 0.999584 -0.49%
> > > >To calculate the DESIRED RESULT on REC# 5, the following calculation
> > > >is needed: (notice 5 days of returns are multiplied together)
> > > >((1.000859 * 0.999705 * 1.000471 * 0.994672 * 1.001945) - 1) * 100 => > > >-0.24
> > > >In order to calculate the DESIRED RESULT for REC# 6 I use the same
> > > >calculation only using RETURN values from REC#'s 2 through 6.
> > > >DESIRED RESULT on REC# 7 uses RETURN values from REC#'s 3 through 7
> > > >It has to be a 5 day window of values that is why REC#'s 1-4 have no
> > > >DESIRED RESULT.
> > > >I'm trying to figure out how to do this without using a cursor....I
> > > >hate cursors.
> > > >Any ideas I've been thinking about if for a couple hours now and
> > > >haven't found a good solution yet.
> > > Hi Izzy,
> > > If the REC# column is guaranteed to hold consecutive numbers (i.e. no
> > > gaps), you can use
> > > SELECT a.RecNo, a."Return",
> > > CASE
> > > WHEN COUNT(b."Return") = 5
> > > THEN (POWER(10.0, SUM(LOG10(b."Return"))) - 1) * 100
> > > ELSE NULL
> > > END AS Result
> > > FROM YourTable AS a
> > > INNER JOIN YourTable AS b
> > > ON b.RecNo BETWEEN a.RecNo - 4 AND a.RecNo
> > > GROUP BY a.RecNo, a."Return";
> > > Note that I'm using POWER(10.0,SUM(LOG10(Value))) to mimic the aggregate
> > > product function that SQL Server lacks. This technique works if all
> > > values are >0, as in your sample. If values =0 or <0 are allwoed as
> > > well, you'll have to use a more complicated expression to get the
> > > aggregate product (Google for SQL Server product aggregate Itzik Ben-Gan
> > > and you'll find it).
> > > If RecNo values can have gaps, *and* you are on SQL Server 2005, you can
> > > generate consecutive rownumbers by using ROW_NUMBER(). If you're still
> > > on SQL Server 2000, you'll need some real nasty work - if you want me to
> > > help with that, you'll have to post CREATE TABLE and SELECT statements
> > > so that I can set up a test environment by using copy and paste.
> > > --
> > > Hugo Kornelis, SQL Server MVP
> > > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis-Hidequoted text -
> > > - Show quoted text -
> > Thanks Hugo I think we are on the right path......but
> > When I run your query it returns only 0.0 for all rows.
> > RecNo is consecutive without gaps.
> > Return column is of type FLOAT.
> > I'm using SQL Server 2005
> > To set up a test:
> > CREATE TABLE #HIST_RETURNS
> > (
> > DAYNUM INT,
> > TOTAL_RETURN FLOAT
> > );
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (1, 1.000859);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (2, 0.999705);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (3, 1.000471);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (4, 0.994672);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (5, 1.001945);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (6, 0.999718);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (7, 0.997262);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (8, 0.997613);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (9, 1.000932);
> > INSERT INTO #HIST_RETURNS (DAYNUM, TOTAL_RETURN)
> > VALUES (10, 0.999584);
> > SELECT A.DAYNUM, A.TOTAL_RETURN,
> > CASE
> > WHEN COUNT(B.TOTAL_RETURN) = 5
> > THEN (POWER(10.0, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
> > ELSE NULL
> > END AS RESULT
> > FROM #HIST_RETURNS AS A
> > JOIN #HIST_RETURNS AS B ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND
> > A.DAYNUM
> > GROUP BY A.DAYNUM, A.TOTAL_RETURN
> > ORDER BY A.DAYNUM;
> > DROP TABLE #HIST_RETURNS;
> > Thanks for the response.- Hide quoted text -
> > - Show quoted text -
> I think I figured it out, by adding more zero's to the "10.0" and
> making it "10.00000".
> SELECT A.DAYNUM, A.TOTAL_RETURN,
> CASE
> WHEN COUNT(B.TOTAL_RETURN) = 5
> THEN (POWER(10.000000, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
> ELSE NULL
> END AS RESULT
> FROM #HIST_RETURNS AS A
> JOIN #HIST_RETURNS AS B ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND
> A.DAYNUM
> GROUP BY A.DAYNUM, A.TOTAL_RETURN
> ORDER BY A.DAYNUM;- Hide quoted text -
> - Show quoted text -
Thanks a bunch for that post Hugo it was exactly what I needed.|||On 20 Sep 2007 06:05:35 -0700, Izzy wrote:
(snip)
>Thanks Hugo I think we are on the right path......but
>When I run your query it returns only 0.0 for all rows.
(...)
>To set up a test:
Hi Izzy,
Thanks for providing a repro. Interesting find!
I've seen your other post with the workaround, but I think I've found a
better way - replace 10.0 (or 10.00000) with 10e0. This results in more
precision in the results from the query:
SELECT A.DAYNUM, A.TOTAL_RETURN,
CASE
WHEN COUNT(B.TOTAL_RETURN) = 5
THEN (POWER(10e0, SUM(LOG10(B.TOTAL_RETURN))) - 1) * 100
ELSE NULL
END AS RESULT
FROM #HIST_RETURNS AS A
JOIN #HIST_RETURNS AS B
ON B.DAYNUM BETWEEN A.DAYNUM - 4 AND A.DAYNUM
GROUP BY A.DAYNUM, A.TOTAL_RETURN
ORDER BY A.DAYNUM;
My guess at the cause is that 10.0 is interpreted by the query engine as
DECIMAL(3,1). The other values are then converted from FLOAT to this
same datatype, causing you to lose quite a bit of precision. Your
workaround of 10.00000 reduced the precision loss, since you now use
DECIMAL(8,6). The 10e0 in my solution is considered to be FLOAT, so
there is no conversion required at all.
Another solution would be to explicitly write CAST(10 AS float)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis