July 10, 2012 at 9:18 am
Hello All,
I am using SQL Server 2000 SP4.
Given a series a dates and a related datapoints I want to the pass that dataset a starting value, multiply the starting value by the first datapoint and then pass that value on to the next record, multuply the newly derived value by the next value and so on.
So given the below -46.683 is my final value. I assume there is a cursor based solution but I'd like to explore something set based.
Thanks if you can help and thanks to all those who have generously helped me in the past.
10 X 1.3 = 13
13 X 2.1= 27.3
27.3 X 1.9 = 51.87
51.87 X -0.9 =-46.683
DECLARE @StartingValue decimal(4,2)
SET @StartingValue = 10
CREATE TABLE #Test
(
TestDate datetime,
TestValue decimal(4,2)
)
INSERT INTO #Test (TestDate, TestValue) VALUES ('2012-01-01 00:00:00.000', 1.3)
INSERT INTO #Test (TestDate, TestValue) VALUES ('2012-01-02 00:00:00.000', 2.1)
INSERT INTO #Test (TestDate, TestValue) VALUES ('2012-01-03 00:00:00.000', 1.9)
INSERT INTO #Test (TestDate, TestValue) VALUES ('2012-01-04 00:00:00.000', -0.9)
SELECT * FROM #Test ORDER BY TestDate
DROP TABLE #Test
July 10, 2012 at 1:27 pm
I think this article, http://www.sqlservercentral.com/articles/T-SQL/68467/, has what you need. You'll need to tweak it to work with what you want but it provides the concepts you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2012 at 1:52 pm
Interesting. Thanks for the link.
July 27, 2012 at 3:52 am
Here's an example using a rCTE:
;WITH MyTable AS (
SELECT TestDate, TestValue
,rn=ROW_NUMBER() OVER (ORDER BY TestDate)
FROM #Test
),
RecursiveProduct AS (
SELECT n=1, TestDate, TestValue, NewValue=CAST(@StartingValue * TestValue AS decimal(4,2))
FROM MyTable
WHERE rn=1
UNION ALL
SELECT a.n+1, b.TestDate, b.TestValue, NewValue=CAST(NewValue * b.TestValue AS decimal(4,2))
FROM RecursiveProduct a
INNER JOIN MyTable b ON b.rn = a.n + 1
)
SELECT *
FROM RecursiveProduct
OPTION(MAXRECURSION 0)
Edit: Oooops! Just realized you can't use it because this is in the SQL 2000 forum, but it was fun anyway.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply