Q:

SQL Server 2012: Can an IIF Function pick up the last assigned value based on a condition

Let's say I have a table that looks like this:
+---------+-----------+------------+
| Company |  Quantity |     Date   |
+---------+-----------+------------+
| A       |     10000 | 2016-08-01 |
| B       |     -5000 | 2016-08-01 |
| C       |      5000 | 2016-08-01 |
| A       |      5000 | 2016-08-02 |
| B       |     -2500 | 2016-08-02 |
| C       |      5000 | 2016-08-02 |
| A       |         0 | 2016-08-03 |
| B       |         0 | 2016-08-03 |
| C       |      5000 | 2016-08-03 |
+---------+-----------+------------+

I am trying to create a view with a column called IssuerLS where if the quantity is +ve, the column value will be 'L' and if the quantity is -ve, the column value will be 'S'. That part is easy with the IIF function but I also want to use the last known value if the Quantity is 0. So my view should look like this:
+---------+----------+------------+----------+
| Company | Quantity |    Date    | IssuerLS |
+---------+----------+------------+----------+
| A       |    10000 | 2016-08-01 | L        |
| B       |    -5000 | 2016-08-01 | S        |
| C       |     5000 | 2016-08-01 | L        |
| A       |     5000 | 2016-08-02 | L        |
| B       |    -2500 | 2016-08-02 | S        |
| C       |     5000 | 2016-08-02 | L        |
| A       |        0 | 2016-08-03 | L        |
| B       |        0 | 2016-08-03 | S        |
| C       |     5000 | 2016-08-03 | L        |
+---------+----------+------------+----------+

Is there a way to do get the IIF Function to use the last known 'L' or 'S' value for each Company if the Quantity is 0?
Thanks.

A:

    ;with cte
    as
    (select *,
    case 
    when qty>0 then 'l' 
    when qty<0 then 'S' 
    else null end as newval
     from #tmp
     )
     select c1.cmp,c1.qty,c1.date,
     case when newval is null then rplcval else newval end as somecol
      from cte  c1
     cross apply
     (
     select top 1 newval as rplcval from cte c2 where c2.date<=c1.date and c1.cmp = c2.cmp
order by date) b

