How to self-join table in a way that every record is joined with the "previous" record?

performance, sql, sql-server, sql-server-2008

Solution

One option is to use a recursive cte (if I'm understanding your requirements correctly):

WITH RNCTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date) rn
        FROM quotes
  ),
CTE AS (
  SELECT symbol, date, rn, cast(0 as decimal(10,2)) perc, closed
  FROM RNCTE
  WHERE rn = 1
  UNION ALL
  SELECT r.symbol, r.date, r.rn, cast(c.closed/r.closed as decimal(10,2)) perc, r.closed
  FROM CTE c 
    JOIN RNCTE r on c.symbol = r.symbol AND c.rn+1 = r.rn
  )
SELECT * FROM CTE
ORDER BY symbol, date

SQL Fiddle Demo

If you need a running total for each symbol to use as the percentage change, then easy enough to add an additional column for that amount -- wasn't completely sure what your intentions were, so the above just divides the current closed amount by the previous closed amount.

Problem

I have a MS SQL table that contains stock data with the following columns: `Id, Symbol, Date, Open, High, Low, Close`. I would like to self-join the table, so I can get a day-to-day % change for `Close`. I must create a query that will join the table with itself in a way that every record contains also the data from the previous session (be aware, that I cannot use yesterday's date). My idea is to do something like this: ``` select * from quotes t1 inner join quotes t2 on t1.symbol = t2.symbol and t2.date = (select max(date) from quotes where symbol = t1.symbol and date < t1.date) ``` However I do not know if that's the correct/fastest way. What should I take into account when thinking about performance? (E.g. will putting UNIQUE index on a (Symbol, Date) pair improve performance?) There will be around 100,000 new records every year in this table. I am using MS SQL Server 2008

Original source