Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "select version()".  Expected tokens may include:  "<table_expr>".  SQLSTATE=42601 SQLCODE=-104

create table t(date1 date,customer_number int, group_number int, role varchar(100))
insert into t
select to_date('2020-06-25','yyyy-mm-dd'),1,0,'None'    from sysibm.sysdummy1 union all
select to_date('2020-06-26','yyyy-mm-dd'),1,0,'None'    from sysibm.sysdummy1 union all
select to_date('2020-06-27','yyyy-mm-dd'),1,0,'None'    from sysibm.sysdummy1 union all
select to_date('2020-06-28','yyyy-mm-dd'),1,0,'None'    from sysibm.sysdummy1 union all
select to_date('2020-06-29','yyyy-mm-dd'),1,0,'None'    from sysibm.sysdummy1 union all
select to_date('2020-06-30','yyyy-mm-dd'),1,11,'Leader' from sysibm.sysdummy1 union all
select to_date('2020-07-01','yyyy-mm-dd'),1,11,'Leader' from sysibm.sysdummy1 union all
select to_date('2020-07-06','yyyy-mm-dd'),1,10,'Member' from sysibm.sysdummy1 
select * from t limit 10


DATE1
CUSTOMER_NUMBER
GROUP_NUMBER
ROLE


2020-07-01
1
11
Leader

2020-06-30
1
11
Leader

2020-06-29
1
0
None

2020-06-28
1
0
None

2020-06-27
1
0
None

2020-06-26
1
0
None

2020-06-25
1
0
None

2020-07-06
1
10
Member


  select date1
        ,customer_number
        ,group_number
        ,case when role='Leader' then 
                  (select t1.group_number
                     from t t1
                    where t1.date1>t.date1
                      and t1.role<>'Leader'
                   order by t1.date1 asc
                   limit 1
                  ) 
             else group_number 
         end as group_number1
        ,role
       ,case when role='Leader' then 
                  (select t1.role
                     from t t1
                    where t1.date1>t.date1
                      and t1.role<>'Leader'
                   order by t1.date1 asc
                   limit 1
                  ) 
             else role 
         end as role1
   from t
order by 1    
      


DATE1
CUSTOMER_NUMBER
GROUP_NUMBER
GROUP_NUMBER1
ROLE
ROLE1


2020-06-25
1
0
0
None
None

2020-06-26
1
0
0
None
None

2020-06-27
1
0
0
None
None

2020-06-28
1
0
0
None
None

2020-06-29
1
0
0
None
None

2020-06-30
1
11
10
Leader
Member

2020-07-01
1
11
10
Leader
Member

2020-07-06
1
10
10
Member
Member

DATE1	CUSTOMER_NUMBER	GROUP_NUMBER	ROLE
2020-07-01	1	11	Leader
2020-06-30	1	11	Leader
2020-06-29	1	0	None
2020-06-28	1	0	None
2020-06-27	1	0	None
2020-06-26	1	0	None
2020-06-25	1	0	None
2020-07-06	1	10	Member