November 10, 2010 at 4:30 pm
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #myTable(
[Date] DATETIME,
[Value] DECIMAL(12,4),
[ValueDifference] DECIMAL(12,4)
)
INSERT INTO #mytable ([Date], [Value])
SELECT 'Oct 17 2007 12:00AM', 5.1709 UNION ALL
SELECT 'Oct 18 2007 12:00AM', 6.5319 UNION ALL
SELECT 'Oct 19 2007 12:00AM', 6.2319 UNION ALL
SELECT 'Oct 20 2007 12:00AM', 6.1319 UNION ALL
SELECT 'Oct 21 2007 12:00AM', 6.5919 UNION ALL
SELECT 'Oct 22 2007 12:00AM', 6.9919 UNION ALL
SELECT * FROM #myTable
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
QUESTION: How does one get the 'Difference'between the 18th and 17th into the [ValueDifference] field for each row in date ASC order ? 🙂
So for the 18th it would be 6.5319 - 5.1709 = 1.361
November 10, 2010 at 4:42 pm
- have a look at Jeff Modens article on tally tables or moving averages.
Those should get you started. http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/
- maybe row_number() can get you a good enough solution. Check books online.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 10, 2010 at 4:47 pm
Thats stuff is 1000 miles over my head...
More direct help would be nice...
November 10, 2010 at 4:55 pm
Modified slightly because I avoid restricted names like the plague, and you left an extra UNION ALL in there. 🙂
Mind you, you're going to probably have to extrapolate from this because I'm sure those two fields are a mere portion of your real DDL and you'll have to account for the proper joining and row_numbering() mechanics.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #myTable(
dt DATETIME,
val DECIMAL(12,4),
valdiff DECIMAL(12,4)
)
INSERT INTO #mytable (dt, val)
SELECT 'Oct 17 2007 12:00AM', 5.1709 UNION ALL
SELECT 'Oct 18 2007 12:00AM', 6.5319 UNION ALL
SELECT 'Oct 19 2007 12:00AM', 6.2319 UNION ALL
SELECT 'Oct 20 2007 12:00AM', 6.1319 UNION ALL
SELECT 'Oct 21 2007 12:00AM', 6.5919 UNION ALL
SELECT 'Oct 22 2007 12:00AM', 6.9919
;with cte AS
(
SELECT dt, val, row_number() OVER( Order By dt) AS RowNum
FROM #mytable
)
UPDATEmt
SETvaldiff = ISNULL( c2.val - c1.val, 0)
FROM
#MyTable AS mt
JOIN
cte AS c1
ONmt.dt = c1.dt
LEFT JOIN
cte AS c2
ONc1.rowNum = c2.Rownum - 1
select * from #mytable
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 4:59 pm
I haven't had a chance to test this, but...
;WITH cteSEQ AS (
SELECT
[Date],
[Value],
ROW_NUMBER() OVER (ORDER BY [Date]) AS rn
FROM #myTable
)
SELECT S1.*,
(S1.Value - S0.Value) AS ValueDifference
FROM cteSEQ AS S1
INNER JOIN cteSEQ AS S0 ON (S1.rn = S0.rn + 1)
You can also replace the SELECT statement with an UPDATE statement to update the source data table.
;WITH cteSEQ AS (
SELECT
[Date],
[Value],
ROW_NUMBER() OVER (ORDER BY [Date]) AS rn
FROM #myTable
)
UPDATE S1
SET ValueDifference = S1.Value - S0.Value
FROM cteSEQ AS S1
INNER JOIN cteSEQ AS S0 ON (S1.rn = S0.rn + 1)
November 10, 2010 at 5:36 pm
Thanks..
Another complication to add,..if you could be so kind...
Add field :valdiffCum (cumulative difference index)
CREATE TABLE #myTable(
dt DATETIME,
val DECIMAL(12,4),
valdiff DECIMAL(12,4),
valdiffCum DECIMAL(12,4)
)
DECLARE @CumStart DECIMAL(12,4)
SET @CumStart = 100
Add up the valdiff to got into the valdiffcum field, but the first value in this field starts at 100 ie @CumStart
so final result would be
row 1 : 100
row 2 : 100 + (6.5319-5.1709) = 101.361
row 3 : 98.639 + (6.2319-6.5319) = 101.061
row 4 : 101.061 + etc
November 10, 2010 at 7:35 pm
Thread closed, thanks !:-)
November 10, 2010 at 7:51 pm
I'm sorry I couldn't reply earlier, but I was in class the rest of the day.
As always I'm glad others jumped in and helped out.:cool:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 10, 2010 at 8:22 pm
ALZDB, can you have a look at this calculation ..for me...
http://www.sqlservercentral.com/Forums/Topic1019024-1291-1.aspx?Update=1
Thanks 🙂
November 15, 2010 at 2:21 am
Done :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply