Subtracting current from previous row by date and ID

Question

I'd like to extend this answer here by including an ID as a grouping column. I tried including a 'group by' clause without success.

+----------+--------------+----------
|NAV_Date  |NetAssetValue |  ID      |
+----------+--------------+---------+
|12/31/2012|        $4,000|     A     |
+----------+--------------+---------+
|03/31/2013|        $5,000|     A     |
+----------+--------------+---------+
|12/31/2012|        $4,000|     B     |
+----------+--------------+---------+
|03/31/2013|        $5,000|     B     |
+----------+--------------+---------+

select NAV_date, NAV_value, (NAV_value / prev_value) - 1
from (select t.*,
             (select top 1 NAV_value
              from YOURTABLENAMEGOESHERE as t2
              where t2.NAV_date < t.NAV_date
              group by Nav_ID, Nav_value
              order by t2.NAV_date desc
             ) as prev_value
      from YOURTABLENAMEGOESHERE as t
     ) as t

score 1 · Accepted Answer · answered Apr 01 '17 at 08:25

Try this:

select 
    NAV_date, 
    NAV_value, 
    (NAV_value / 
        (select top 1 NAV_value
        from YOURTABLENAMEGOESHERE as t2
        where t2.NAV_date < t.NAV_date
        order by t2.NAV_date desc) - 1
from 
    YOURTABLENAMEGOESHERE as t

Subtracting current from previous row by date and ID

1 Answers1