salesforce – SOQL – Count of records where the parent hasn’t had child records in 1 year

I am trying to create a SOQL query (and later a report) in Salesforce that generates the following data:

Count of Child records grouped by Parent records where the Parent does not have child records created in the past year.

I tried this first; however, salesforce returned an error stating ‘Nesting of semi join sub-selects is not supported’.

SELECT Id, Name, Training_Course__c
FROM Training_Record__c
WHERE Training_Course__c IN (
  FROM Training_Course__c
    SELECT Training_Course__c
    FROM Training_Record__c
    WHERE CreatedDate != Last_n_Days:365

The requirements are to use a single query to obtain the data requested without forcing them to run two reports and use Excel to get the data. I’m not sure if that’s possible given Salesforce’s constraints.

Is this possible in SOQL? If so, what can I do differently?

Read more here: Source link