title: Getting last non empty value within the same table
body: <p>I have the following table and data.</p>
  
  <pre><code>create table items
  (
      itemid int
      ,userid varchar(10)
      ,itemtype varchar(10)
      ,value varchar(10)
  );
  
  insert into items values (101,'usr1','CST','');
  insert into items values (101,'usr1','GST','');
  
  insert into items values (100,'usr1','Data','a25');
  insert into items values (100,'usr1','GST','');
  
  insert into items values (99,'usr3','Data','a50');
  
  insert into items values (98,'usr3','CST','');
  insert into items values (98,'usr3','GST','');
  
  insert into items values (97,'usr3','CST','');
  
  insert into items values (96,'usr3','Data','a25');
  insert into items values (96,'usr3','GST','');
  
  insert into items values (95,'usr3','Data','a50');
  insert into items values (95,'usr3','GST','');
  </code></pre>
  
  <p>This is a invoice lines table that contains details of each line within an item.  Some invoice lines do not have line of the type 'Data'. For these records, we need to traverse the table and find the <strong>next lowest itemid</strong> that has 'Data' value in it by matching on the userid, take the value and get it as output.</p>
  
  <p>Here is the expected output -</p>
  
  <pre><code>itemid,userid,itemtype,value
  101,usr1,CST,a25
  101,usr1,GST,a25
  98,usr3,CST,a25
  98,usr3,GST,a25
  97,usr3,CST,a25
  </code></pre>
  
  <p>As can be seen, itemid 101 gets the value from itemid 100. Similarly itemid's 98 and 97 get the valeus from itemid 96.</p>
  
  <p>I have written the following query to obtain all the invoices not containing data -</p>
  
  <pre><code>;with cte_groupdata
  as
  (
      select  itemid
              ,userid
              ,case 
                  when itemtype = 'Data' then 1
                  else 0
              end as rn
      from    items
      group by itemid
              ,userid
              ,case 
                  when itemtype = 'Data' then 1
                  else 0
              end
  )
  ,cte_validdata
  as
  (
      select  itemid
              ,userid
              ,count(*) as total
      from    cte_groupdata
      group by itemid
              ,userid
  )
  select  vld.itemid
          ,vld.userid
          ,it.itemtype
  from    cte_validdata vld
          join items it
              on vld.userid = it.userid
              and vld.itemid = it.itemid
  where   vld.total = 1
              and it.itemtype &lt;&gt; 'Data';
  </code></pre>
  
  <p>I am getting the required invoices on which I need to do the processing. I know I need to write a co-related subquery. I am just not able to understand how to put the conditions. This is a prod data and we don't have the permission to create UDF's or procedures.</p>