How to obtain a value from previous day in a query

Question

Given the data table below, how can you show the score for these date range: 3/10 to 3/12?

The formula for score is today's score=(today's avg5daysprice *2.15)/ yesterday's score. For example for 3/10 score = (126.11*2.15)/10.36

The data lives in both Sql server and mysql.

symbol  tdate      price   avg5daysprice  score
-----------------------------------------------
aapl    3/9/2015   127.14  126.6,         10.36
aapl    3/10/2015  125.32  126.11         null
aapl    3/11/2015  128.15  127.25         null
aapl    3/12/2015  124.48  125.66         null

dario · Answer 1 · 2015-03-20T19:48:25.360

1

In SQL Server 2012 you can use the LAG function to access data from the previous row. Try this:

SELECT symbol
      ,tdate
      ,price
      ,avg5daysprice
      ,COALESCE((avg5daysprice * 2.15) / LAG(score, 1, NULL) OVER (ORDER BY tdate), score) AS score
FROM YourTable

For a cross-RDBMS solution I would go with a self-join.

edited Mar 20 '15 at 19:48

answered Mar 20 '15 at 19:03

dario

5,149
12
28
32

The question is labeled sql-server and t-sql. I had the same answer typed up. – rhholt Mar 20 '15 at 19:06
Here is a workaround in MySql from a previous post. http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – rhholt Mar 20 '15 at 19:07
Thank you King Code and everyone else, But lag only gives me the value for the next day which is 3/10 and 3/11 and 3/12 are still null – Mar 21 '15 at 01:51

user3281440 · Accepted Answer · 2015-03-20T20:12:44.270

CTE is the solution in Sql Server, below the code

WITH Value_CTE (symbol, tdate,avg5day, scoreAnt)    AS (
SELECT symbol, tdate,avg5day, score
FROM ScoreTable
WHERE score IS NOT NULL
YNION ALL
SELECT B.symbol, B.tdate, B.avg5day, cast(((B.avg5day*2.15)/A.scoreAnt) as decimal(8,2)) as score
FROM  value_CTE A
INNER JOIN ScoreTable B ON DATEADD(DAY,-1,B.tdate) = A.tdate
)
 -- Define the outer query referencing the CTE name.
 SELECT symbol, tdate, avg5day, scoreAnt
 FROM  value_CTE

Result

symbol               tdate      avg5day                                 scoreAnt

aapl                 2015-03-09 126.60                                  10.36
aapl                 2015-03-10 126.11                                  26.17
aapl                 2015-03-11 127.25                                  10.45
aapl                 2015-03-12 125.66                                  25.85

In code above use U Instead of Y in YNION ALL statement, that was the cause of getting error while submiting the response. — user3281440, Mar 20 '15 at 20:10

How to obtain a value from previous day in a query

2 Answers2