CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20342

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.

Upvotes: 0

Views: 46

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

You can use row_number() to left join the previous dates.

select 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
    (select id, px, row_number() over (order by ID) as rn, as_of_date
    from Price_table where px not in ('N.A.') and SEDOL in ('B1Z2900')) t1
left join 
    (select id, px, row_number() over (order by ID) as rn, as_of_date
    from Price_table where px not in ('N.A.') and SEDOL in ('B1Z2900')) t2 on t1.rn = t2.rn + 1

Upvotes: 0

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions