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" 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" 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