I am using Teradata SQL and have the following data:

I want to retrieve all the rows after per ID ever since when Complaint is > 0 ordered by ID, MonthID.

Expected Output:

Can someone please help?

Thanks!

Last attempt code:

select a.*, 
row_number() over (partition by ID order by Complaint, MonthID) 
from table a

CodePudding user response：

You can use a table expression to compute the initial month per ID. Then filtering out becomes easier. For example:

select t.*
from t
join (
  select id, min(monthid) as mm from t where complaint > 0 group by id
) x on t.id = x.id
where t.monthid >= x.mm

CodePudding user response：

If there are more than one zero values per id you need to use gaps and islands approach:

with cte1 as (
    select id, monthid, acc, complaint, case when lag(complaint, 1, 0) over (partition by id order by monthid) = 0 and complaint > 0 then 1 end as newgrp
    from t
), cte2 as (
    select cte1.*, sum(newgrp) over (partition by id order by monthid) as grpnum
    from cte1
)
select *
from cte2
where grpnum is not null
order by id, monthid