SSA SSA - 7 months ago 11
SQL Question

Carry the last value forward for items no longer updated in a table

I have a table in SQL server with accumulated returns over time for multiple securities.

The table looks like this:

report_date / security_id / return_usd
---------------------------------------
2016-02-21 / security_a / 2.0
2016-02-22 / security_a / -1.2
2016-02-23 / security_a / -2.7
2016-02-21 / security_b / 5.2


The problem is that the accumulated return disappears for securities which have been sold or are matured (security b as an example above).

I need to continue to carry the last value (return_usd) forward for these securities as in the example below:

report_date / security_id / return_usd
---------------------------------------
2016-02-21 / security_a / 2.0
2016-02-22 / security_a / -1.2
2016-02-23 / security_a / -2.7
2016-02-21 / security_b / 5.2
2016-02-22 / security_b / 5.2
2016-02-23 / security_b / 5.2


Unfortunately I haven't been able to figure it out myself so it would be much appreciated if anyone could help me.

Answer

It looks like you want a row for all securities and dates, and then to carry forward the sum. You can generate the rows using CROSS JOIN. Then, in SQL Server 2012+, you can use LAG():

select d.report_date, s.security_id,
       coalesce(t.return_usd,
                lag(t.return_usd) over (partition by s.security_id
                                        order by (case when t.return_usd is not null then 1 else 2 end), d.report_date
                                       )
               ) as return_usd
from (select distinct report_date from t) d cross join
     (select distinct security_id from t) s left join
     t
     on t.report_date = d.report_date and t.security_id = s.security_id;

Note: This fills in any NULL value with the previous non-NULL value. If you have no gaps before the end, then this should be what you want.

EDIT:

This can also be written using outer apply or a correlated subquery:

select d.report_date, s.security_id,
       (select top 1 t.return_usd
        from t
        where t.report_date <= d.report_date and t.security_id = d.security_id and
              t.return_usd is not null
        order by t.report_date desc
       ) as return_usd
from (select distinct report_date from t) d cross join
     (select distinct security_id from t) s left join
     t
     on t.report_date = d.report_date and t.security_id = s.security_id;

This will work on earlier (supported) versions of SQL Server.