CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Nisha Nethani
Nisha Nethani

Reputation: 109

How to get the last not null value from previous rows

I know that there are a lot of solutions for this but unfortunately I cannot use partition or keyword TOP. Nothing I tried on earlier posts works.

My table looks like this: enter image description here

The result I want is when any completion percentage is NULL it should get the value from last non-value completion percentage, like this:

enter image description here

Upvotes: 2

Views: 1519

Answers (2)

Sahi
Sahi

Reputation: 1484

you can try this: 

SELECT t2.project_name,t2.sequence,
case when percentage is null then (select percentage from table1  t1 where t1.sequence=t2.sequence-1 )
else t2.percentage end as percentage
FROM table t2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

This is most easily done using outer apply:

select t.projectname, t.sequence,
       coalesce(t.completion_percentage, t2.completion_percentage) as completion_percentage
from t outer apply
     (select top 1 t2.*
      from t t2
      where t2.projectname = t.projectname and
            t2.completion_percentage is null and
            t2.sequence < t.sequence
      order by t2.sequence desc
     ) t2;

Upvotes: 3

Related Questions