Getting last non empty value within the same table

Question

I have the following table and data.

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','');

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 next lowest itemid that has 'Data' value in it by matching on the userid, take the value and get it as output.

Here is the expected output -

itemid,userid,itemtype,value
101,usr1,CST,a25
101,usr1,GST,a25
98,usr3,CST,a25
98,usr3,GST,a25
97,usr3,CST,a25

As can be seen, itemid 101 gets the value from itemid 100. Similarly itemid's 98 and 97 get the valeus from itemid 96.

I have written the following query to obtain all the invoices not containing data -

;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 <> 'Data';

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.

Answer 1

Using cross join lateral should be sufficient for this:

create table items
(
    itemid int
    ,userid varchar(10)
    ,itemtype varchar(10)
    ,value varchar(10)
);

insert into items values
 (101,'usr1','CST',''),
 (101,'usr1','GST',''),
 (100,'usr1','Data','a25'),
 (100,'usr1','GST',''),
 (99,'usr3','Data','a50'),
 (98,'usr3','CST',''),
 (98,'usr3','GST',''),
 (97,'usr3','CST',''),
 (96,'usr3','Data','a25'),
 (96,'usr3','GST',''),
 (95,'usr3','Data','a50'),
 (95,'usr3','GST','');

select
       i.itemid, i.userid, i.itemtype, cjl.datavalue
from items i
cross join lateral (
    select value as datavalue
    from items i2
    where i.userid = i2.userid 
    and i.itemid > i2.itemid 
    and i2.itemtype = 'Data'
    and i2.value <> ''
    order by i2.itemid desc
    limit 1
    ) cjl
where i.itemtype <> 'Data'

itemid | userid | itemtype | datavalue
-----: | :----- | :------- | :--------
   101 | usr1   | CST      | a25      
   101 | usr1   | GST      | a25      
    98 | usr3   | CST      | a25      
    98 | usr3   | GST      | a25      
    97 | usr3   | CST      | a25      
    96 | usr3   | GST      | a50      

dbfiddle here

Answer 2

You should join the table to itself with appropriate conditions. Use lateral join to get only one row from the joined rows:

select 
    i1.itemid, i1.userid, i1.itemtype,
    case when i1.value <> '' then i1.value else i2.value end as value
from items i1
cross join lateral (
    select value
    from items i2
    where i1.userid = i2.userid 
    and i1.itemid > i2.itemid 
    and i2.value <> ''
    order by i2.itemid desc
    limit 1
    ) i2
where i1.itemid in (
    select itemid
    from items
    group by itemid
    having string_agg(value, '') = '')
order by i1.itemid desc, i1.userid, i1.itemtype;

 itemid | userid | itemtype | value 
--------+--------+----------+-------
    101 | usr1   | CST      | a25
    101 | usr1   | GST      | a25
     98 | usr3   | CST      | a25
     98 | usr3   | GST      | a25
     97 | usr3   | CST      | a25
(5 rows)    

Answer 3

You can try a NOT EXISTS clause to filter down the rows to the ones that have no data, and then combine that with a join that includes a correlated subquery to get the missing data:

select i.itemid, i.userid, i.itemtype,
       iprev.value
  from items i
  join items iprev
    on iprev.userid = i.userid
   and iprev.itemtype = 'Data'
   and iprev.value <> ''
   and iprev.itemid = (select max(i2.itemid)
                         from items i2
                        where i2.itemid < i.itemid
                          and i2.userid = iprev.userid
                          and i2.itemtype = iprev.itemtype
                          and i2.value <> '')
 where i.value = ''
   and not exists (select null
                     from items i2
                    where i2.itemid = i.itemid
                      and i2.userid = i.userid
                      and i2.value <> '')