I need to run a query to compare dates on child objects. We have a custom object Job Placements (child of Contacts) with Start Date and End Date fields. I need to find all Job Placements where the Start Date is within 30 days of an End Date from another Job Placement on the same Contact. Is this possible with SOQL or do I need to write a trigger to mark these records when they are created?

//Query to find ID and Start/End Dates of Placements This Year
List<ts2__Placement__c> plList = new List<ts2__Placement__c>([SELECT ID,         
ts2__Employee__c, ts2__Employee__r.ID, ts2__Start_Date__c, 
Actual_End_Date__c FROM ts2__Placement__c WHERE ts2__Start_Date__c = 

//Query to find IDs of Employees with Placements This Year
List<ts2__Placement__c> plEmList = new List<ts2__Placement__c>([SELECT ID, 
ts2__Employee__c, ts2__Employee__r.ID FROM ts2__Placement__c WHERE 
ts2__Start_Date__c = THIS_YEAR]);

//Set of IDs of Employees with Placements This Year
Set<ID> plSet = new Set<ID>();
for(ts2__Placement__c p :plEmList){
  • Could the contact have more than 2 Job Placement? If yes, what Job Placements I should get to compare? – m Peixoto Aug 15 '17 at 19:19
  • Yes, contact can have more than 2 Job Placement. So far I have created a Set to contain Job Placement IDs from this year, and a Set to contain Contact IDs from those Job Placements. I am trying to conceptualize how I will grab Job Placements where ContactId in :ContactSet and Start Date <= 30 days from other contact Job Placement End Date. – Jean Grey Aug 15 '17 at 19:31
  • I need to compare dates across multiple Job Placement records when the related Contacts are the same. Example: Contact 1 Job Placement 1A Start 1/1/17 End 3/30/17 Job Placement 1B Start 4/1/17 End 5/1/17 Job Placement 1C Start 8/1/17 End 12/1/17 Contact 2 Job Placement 2A Start 1/1/17 End 1/30/17 Job Placement 2B Start 4/1/17 End 7/30/17 Job Placement 2C Start 8/15/17 End 12/31/17 Query/logic should return Job Placements 1B and 2C. – Jean Grey Aug 15 '17 at 19:44
  • If you can't share your code then possibly members will vote your question for closure – Santanu Boral Aug 15 '17 at 19:45
  • Edit: code shared. – Jean Grey Aug 15 '17 at 19:54

In typical RDBMS, Window or Analytical functions may help but Salesforce doesn't have equivalent of such features so don't think you can achieve that using SOQL alone.

  • I know it's possible to mark applicable Job Placements with a Trigger as they are created, but is it possible to display records that meet this criteria on a Visualforce Page with Apex behind? – Jean Grey Aug 16 '17 at 1:29
  • Since Apex is programming environment, you can build the data however you want to present (of course without governor limits). Did you have specific question on that? – realfire Aug 16 '17 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.