Prev: sql commands for datediff
Next: Diferent times for execution the same code
From: Frank on 13 Jan 2010 11:33
Hi Dan,

Amazing how the simplest solution is often the best.
The code you posted appears to work as expected, so I'll try using
that.

Many thanks,
Frank.
From: Dan on 13 Jan 2010 11:54

"Frank" <francis.moore(a)gmail.com> wrote in message
news:ac4bb3ad-21e7-49c9-a31c-7432557b780e(a)j5g2000yqm.googlegroups.com...
> Hi Dan,
>
> Amazing how the simplest solution is often the best.
> The code you posted appears to work as expected, so I'll try using
> that.
>
> Many thanks,
> Frank.

To be honest I can't take credit for it entirely - it's based on memory of
queries I've seen posted in this newsgroup from time to time and I use a
similar format myself for some of my own application code, it's just
something that sprang to mind when I saw your post and I thought I'd post
it. I'm sure that had I not done so someone else would have done shortly.
Glad to have been of help though :)

--
Dan

From: Plamen Ratchev on 13 Jan 2010 12:03
Tom's solution will be best. Here is alternative using recursive CTE (not the best performance, but for small data set
may do fine).

;WITH Ranked AS (
SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM Foo),
Results AS (
SELECT CAST(1 + value AS DECIMAL(38, 20)) AS calc, rn
FROM Ranked
WHERE rn = 1
UNION ALL
SELECT CAST((1 + F.value) * R.calc AS DECIMAL(38, 20)), f.rn
FROM Ranked AS F
JOIN Results AS R
ON R.rn = F.rn - 1)
SELECT TOP (1) calc - 1
FROM Results
ORDER BY rn DESC;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 13 Jan 2010 17:13
Dan (news(a)worldofspack.com) writes:
> create table testing (value decimal(10,8))
>
> insert into testing values (0.029000)
> insert into testing values ( 0.012000)
> insert into testing values ( 0.038000)
> insert into testing values ( 0.011000)
> insert into testing values ( -0.048000)
>
>
> declare @myvalue decimal(20,18)
> set @myvalue = 1
>
> select @myvalue = @myvalue * (1 + value) from testing
>

WARNING! This relies on undefined behaviour! This may work the way you
want, but sddently one day with change of query plans it will break.
I recommend against it.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Dan on 14 Jan 2010 04:59

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CFFEC311B6E3Yazorman(a)127.0.0.1...
> Dan (news(a)worldofspack.com) writes:
>> create table testing (value decimal(10,8))
>>
>> insert into testing values (0.029000)
>> insert into testing values ( 0.012000)
>> insert into testing values ( 0.038000)
>> insert into testing values ( 0.011000)
>> insert into testing values ( -0.048000)
>>
>>
>> declare @myvalue decimal(20,18)
>> set @myvalue = 1
>>
>> select @myvalue = @myvalue * (1 + value) from testing
>>
>
> WARNING! This relies on undefined behaviour! This may work the way you
> want, but sddently one day with change of query plans it will break.
> I recommend against it.
>

I knew there was some reason for not doing it. Cheers for adding the warning
note.

--
Dan

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: sql commands for datediff
Next: Diferent times for execution the same code