CODEWITHSUNDEEP
sqlsql-servert-sql
Vadim S
Vadim S

Reputation: 23

TSQL "select" or temp tables/cursors

I am working on a project with financial data. I have a table that contains closePrice column. I need to select the data in a way where the closePrice for the previous time period becomes the open price for the next time period.

Data: 

ClosePrice
0.77512
1.18488
0.87144
132.297
1.23644
151.804

Desired Result:

OpenPrice  ClosePrice
1.18488    0.77512
0.87144    1.18488
132.297    0.87144
1.23644    132.297
151.804    1.23644
           151.804

My question is... is there a way to accomplish this with a select statement without resorting to temp tables and/or cursor processing ?

Upvotes: 0

Views: 67

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want lead() function available fron SQL Server+12 

select LEAD(ClosePrice) over(order by ?) as OpenPrice, ClosePrice
from table t

You could also use apply operator

select OpenPrice, ClosePrice 
from table t outer apply (
    select top 1 ClosePrice as OpenPrice
    from table where ? = t.?+1) tt

However, you would required to specify the name of column ? (i.e. id or row_no) that could specify your column ordering

Upvotes: 6

Related Questions