0

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 = 
THIS_YEAR]);


//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){
    if(!plSet.contains(p.ts2__Employee__r.ID)){
        plSet.add(p.ts2__Employee__r.ID);
    }
}
5
  • Could the contact have more than 2 Job Placement? If yes, what Job Placements I should get to compare?
    – m Peixoto
    Aug 15, 2017 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, 2017 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, 2017 at 19:44
  • If you can't share your code then possibly members will vote your question for closure Aug 15, 2017 at 19:45
  • Edit: code shared.
    – Jean Grey
    Aug 15, 2017 at 19:54

1 Answer 1

0

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.

2
  • 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, 2017 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?
    – metasync
    Aug 16, 2017 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .