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.
;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