calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

calculation

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-15 : 04:40:08

tblMain has two fields, Date and Value:

select Date, Value from tblMain

Date value
31 aug 2010 1.0
07 sep 2010 1.5
14 sep 2010 7.2
21 sep 2010 6.3
28 sep 2010 6.8
30 sep 2010 6.812

Without using a cursor, how is it possible to do the following from the above data in a select query.
Notice that you are going through the dates one by one and:
next date/previousdate...

This is what I want to get please:

final value = ((((1.5/1.0)/1.0) * 100)/100+1) * ((((7.2/1.5)/1.5) * 100)/100+1) * ((((6.3/7.2)/7.2) * 100)/100+1) * ((((6.8/6.3)/6.3) * 100)/100+1) * ((((6.812/6.8)/6.8) * 100)/100+1)

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-10-15 : 05:37:03

Hi

DECLARE @tblMain AS TABLE (dateC DATETIME, valC FLOAT)
DECLARE @tblMain2 AS TABLE (ID INT IDENTITY, dateC DATETIME, valC FLOAT)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '31 aug 2010', 1.0)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '07 sep 2010', 1.5)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '14 sep 2010', 7.2)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '21 sep 2010', 6.3)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '28 sep 2010', 6.8)
INSERT INTO @tblMain ( dateC, valC ) VALUES ( '30 sep 2010', 6.812)

INSERT INTO @tblMain2( dateC, valC )
select DateC, ValC from @tblMain ORDER BY dateC

select DateC, ValC from @tblMain2
DECLARE @i AS INT
DECLARE @cnt AS INT
DECLARE @finalValue AS FLOAT
SET @i = 1
SET @cnt = 0
SELECT @cnt = COUNT(ID) FROM @tblMain2
DECLARE @valC1 FLOAT
DECLARE @valC2 FLOAT
SET @finalValue = 1.0
WHILE (@i < @cnt)
BEGIN
--final value = ((((1.5/1.0)/1.0) * 100)/100+1) * ((((7.2/1.5)/1.5) * 100)/100+1) * ((((6.3/7.2)/7.2) * 100)/100+1) * ((((6.8/6.3)/6.3) * 100)/100+1) * ((((6.812/6.8)/6.8) * 100)/100+1)
SELECT @valC1 = valC FROM @tblMain2 WHERE id = @i
SELECT @valC2 = valC FROM @tblMain2 WHERE id = (@i + 1)
SET @finalValue = @finalValue * ((((@valC2/@valC1)/ @valC1) * 100)/ 100 + 1)
SET @i = @i + 1
END
SELECT ((((1.5/1.0)/1.0) * 100)/100+1) * ((((7.2/1.5)/1.5) * 100)/100+1) * ((((6.3/7.2)/7.2) * 100)/100+1) * ((((6.8/6.3)/6.3) * 100)/100+1) * ((((6.812/6.8)/6.8) * 100)/100+1)
SELECT @finalValue

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-15 : 06:12:05

Or something like this:


DECLARE @final_value DECIMAL(18, 3) = 1;

WITH CTE AS
(
   SELECT T.date, T.value, ROW_NUMBER() OVER(ORDER BY T.date) AS row_num
     FROM tblMain AS T
)
SELECT @final_value = @final_value * ((((N.value / P.value) / P.value) * 100) / 100 + 1)
  FROM CTE AS N
       INNER JOIN
       CTE AS P
       ON P.row_num = N.row_num - 1;

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-15 : 10:32:54

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources