I have a table with the following values
So the output should be something like, if I could see a customer with a material, who has an entry in the past 3 years, then the status flag should be Yes, other wise the status flag should be no.
I was thinking about counting the given occurences of a customer/material pair, for the past 3 years, and check the count to set the status flag. But confused how I would use windowing function?
select customer_name, material, count(*) from table where datediff(year, date, current_date) <=3 group by customer_name, material
and then join this table, but I feel this approach is wrong as it does not account for rolling status.