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:

  • Code to validate SQL Scripts
  • Limitations on script size when executing on SQL Server Express?
  • List all columns referenced in all procedures of all databases
  • NEWID() SQL Server Function in Entity Framework
  • Order by NULL while select distinct is used
  • Improve Sql Query with select max() function in where clause
  • +---------+----------+------------+----------+
    | 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.

    2 Solutions collect form web for “SQL Server 2012: Can an IIF Function pick up the last assigned value based on a condition”

        ;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
    

    can be done using case and outer apply.

    SELECT  *,
            CASE WHEN Quantity = 0 THEN CASE WHEN prevQuantity > 0 THEN 'L' ELSE 'S'END
                 ELSE CASE WHEN Quantity > 0 THEN 'L' ELSE 'S' END
            END
    FROM    Table1 t1
            OUTER APPLY (SELECT TOP 1
                                Quantity prevQuantity
                         FROM   Table1 t2
                         WHERE  t2.Company = t1.Company
                                AND t2.Date < t1.Date
                                AND t2.Quantity <> 0
                         ORDER BY [Date] DESC
                        ) t2
    

    if you want to use IIF then

    SELECT  *,
            IIF(Quantity <> 0, IIF(Quantity > 0, 'L','S'), IIF(prevQuantity > 0, 'L','S'))
    FROM    Table1 t1
            CROSS APPLY (SELECT TOP 1
                                Quantity prevQuantity
                         FROM   Table1 t2
                         WHERE  t2.Company = t1.Company
                                AND t2.Date <= t1.Date
                                AND t2.Quantity <> 0
                         ORDER BY [Date] DESC
                        ) t2
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.