Trying to get Price Difference and Percent Difference

I am trying to find the daily price difference and the daily percent difference, in one field, in one table. Here is the code that I am testing.

SELECT DISTINCT t1.as_of_date,
   t1.ID,
   t1.PX,
   (case when datediff(day,t1.as_of_date,t2.as_of_date) = 1 then CAST(t2.PX AS DECIMAL(10,2) - CAST(t1.PX AS DECIMAL(10,2)))) As PriceDiff,
   (case when datediff(day,t1.as_of_date,t2.as_of_date) = 1 then CAST(t2.PX AS DECIMAL(10,2) / CAST(t1.PX AS DECIMAL(10,2)))-1 As PercentDiff
FROM Price_Table t1
INNER JOIN Price_Table t2
   ON t1.ID = t2.ID
WHERE t1.PX not in ('N.A.')
   AND t1.SEDOL IN ('B1Z2900')
Order By t1.as_of_date

Here is a sample of my data (first three fields), with the expected results (last two fields named ‘PriceDiff’ & ‘PercentDiff’).

as_of_date  ID          PX       PriceDiff      PercentDiff
1/1/2018    11986003    3.43
1/2/2018    11986003    3.37     -0.06          -0.017492711
1/3/2018    11986003    3.34     -0.03          -0.008902077
1/4/2018    11986003    3.33     -0.01          -0.002994012
1/5/2018    11986003    3.42      0.09           0.027027027

I am using SQL Server.

Answer

You seem to want lag():

SELECT 
    as_of_date,
    ID,
    PX,
    LAG(CAST(PX AS DECIMAL(10,2))) OVER(ORDER BY as_of_date) 
        - CAST(PX AS DECIMAL(10,2)) As PriceDiff,
    (LAG(CAST(PX AS DECIMAL(10,2))) OVER(ORDER BY as_of_date))
        / CAST(PX AS DECIMAL(10,2)) AS PercentDiff
FROM Price_Table 
WHERE 
    PX <> 'N.A.'
    AND t1.SEDOL = 'B1Z2900'
ORDER BY as_of_date