SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 calculation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1422 Posts

Posted - 10/15/2012 :  04:40:08  Show Profile  Reply with Quote

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

USA
190 Posts

Posted - 10/15/2012 :  05:37:03  Show Profile  Visit sql-programmers's Homepage  Reply with Quote

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/
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
264 Posts

Posted - 10/15/2012 :  06:12:05  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1422 Posts

Posted - 10/15/2012 :  10:32:54  Show Profile  Reply with Quote
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000