SQL-Query doesn't work - why? - 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

General SQL Server Forums

New to SQL Server Programming

SQL-Query doesn't work - why?

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2008-08-29 : 05:06:34

Hello everyone!

Once again I try to analyze time series from the DAX (German Stock Exchange Index). My goal is to create a query that has a fourth column that shows the difference of the price in comparison to the price a second before.
My table:
select * from tbl order by sdatetime
142 2008-03-25 09:04:36.000 6443.45
163 2008-03-25 09:04:35.000 6443.48
184 2008-03-25 09:04:34.000 6443.36

As a result I wish to have something like:

1 2008-03-25 09:04:36.000 6443.45 -0.3
2 2008-03-25 09:04:35.000 6443.48 0.12
3 2008-03-25 09:04:34.000 6443.36 .....
. ....................... ....... .....

I want it as one query without second table - at least not a permanent one.

My approach which doesnt work is following:

select t.id, t.Counter, t.sdatetime, t.price, t.price-
(select row_number() over(order by sdatetime desc)as Counter, price from tbl) tt
where tt.Counter = t.Counter+1
from
(select id, ROW_NUMBER() OVER(ORDER BY sdatetime desc) AS Counter,sdatetime, price
from tbl) t

Many thanks in advance and greetings from Vienna

Landau

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 05:12:44

[code]SELECT IDENTITY(int,1,1) AS Seq,* INTO #Temp
FROM tbl
order by sdatetime

SELECT t1.*,t1.price-COALESCE(t2.price,0) AS diff
FROM #Temp t1
LEFT JOIN #Temp t2
ON t2.Seq=t1.Seq+1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 05:27:59

or:-

;With Your_CTE(seq,id,Counter, sdatetime,price)AS
(select row_number() over(order by sdatetime),
id,Counter, sdatetime,price
from tbl
)

select c1.*,c1.price-coalesce(c2.price,0)
from Your_CTE c1
LEFT JOIN Your_CTE c2
ON c2.Seq=c1.Seq+1

if sql 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources