How to identify weekly clients that appear in one table and not another in sql?

Question

Hey, all right, guys?

Well, I need to identify weekly clients who have an active plan but didn’t use the plan that week.

I have two tables, one that contains the type of client’s plan and another that includes the customers who used the service.

How do I give a Join between these two tables so that it brings me per week customers who had an active plan but did not use the service in a given week?

PS: Clients have unique Id and both tables have this id.

In the table of customers who used service, the date is already in date_trunc week.

In the table of purchased plans , the date of purchase and closure are also already in date_trunc week.

And I’ve also created an auxiliary table with every week of 2020 to group these two tables.

Answer 1

Good evening Rogério! I could not understand very well the problem...

Check if this is what you need: The simplest would be to create a job in your database that runs 1 time a week and put the query return on it:

Select id, name from cli_plano where data_desativado > to_date('DD/MM/YYYY', '01/01/2020') and id not in (select id from cli_uso where data_uso between to_date('DD/MM/YYYY', '01/01/2020') - 7 and to_date('DD/MM/YYYY', '01/01/2020') )

I hope I have understood correctly the problem, Anything send me an example table with the same fields. Hug.