how to take rows after a certain criteria SQL

Question

I am using Teradata SQL and have the following data:

ID	MonthID	Acc	Complaint
1	202202	5	1
1	202203	4	2
1	202204	3	0
2	202202	2	0
2	202203	3	1
2	202204	2	3
3	202202	1	0
3	202203	2	0
3	202204	3	0

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

Expected Output:

ID	MonthID	Acc	Complaint
1	202202	5	1
1	202203	4	2
1	202204	3	0
2	202203	3	1
2	202204	2	3

Can someone please help?

Thanks!

Last attempt code:

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

Can you share your last coding attempt at this problem? Maybe it just needs some quick fix. @samronaldo309 — lemon, Jun 14 '22 at 13:52
Your query assigns a ranking to your current rows, have you though about adding some filtering condition inside a `WHERE` clause, using aggregation functions or something like that? — lemon, Jun 14 '22 at 13:58
@lemon nope, I am bit lost at the moment on how to solve this — samronaldo309, Jun 14 '22 at 13:59

score 1 · Answer 1 · answered Jun 14 '22 at 14:01

1

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

answered Jun 14 '22 at 14:01

The Impaler

45,731
9
39
76

Thanks for that, is there another way of doing it. I have huge dataset and feel like it will take a lot of time to process this and I have limited resource (memory) – samronaldo309 Jun 14 '22 at 14:04
@samronaldo309 You can always materialize the subquery into a table, index it by `id`, and then run this query against it. With the index in place it should be fast. – The Impaler Jun 14 '22 at 14:05
oh okay, can you please advise me on the syntax to create a temporary table with index? – samronaldo309 Jun 14 '22 at 14:06
@samronaldo309 Unfortunately, I'm not the best person to ask about the nitty-gritty details since I don't have access to a teradata database anymore. – The Impaler Jun 14 '22 at 14:09
@SalmanA yes, please see the dataset provided – samronaldo309 Jun 14 '22 at 14:10

score 0 · Answer 2 · answered Jun 14 '22 at 17:04

A simple cumulative sum over complaint codes match you expected result:

select *
from tab
qualify
   sum(Complaint) -- inital rows with no complaint get 0
   over (partition by ID 
         order by MonthID
         rows unbounded preceding) > 0

If the logic is more complex a CASE might help:

sum(case when Complaint > 0 then 1 else 0 end)

how to take rows after a certain criteria SQL

2 Answers2