+ Start a Discussion
Jean Grey 10Jean Grey 10 

Compare dates on child objects

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?
Best Answer chosen by Jean Grey 10
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean

If you have not found solution yet, please try this and let me know. I have tested this code and it is working fine
In this code, replace
Job_Placement with your table name
Start_Date with your exact field name start date
End_Date with your exact field name end date.
Dont change __c or __r


List<Contact> contList = [SELECT Id, Name,(SELECT ID, Name, Start_Date__c, End_Date__c FROM Job_Placements__r) FROM Contact];
        Integer count ;
        Set<Id> JPId = new set<Id>();
        List<Job_Placement__c> finalList = new List<Job_Placement__c>();
        for(Contact c : contList)
             {     for(Job_Placement__c JPOuterLoop : c.Job_Placements__r)
                   {
                    for(Job_Placement__c JPInnerLoop : c.Job_Placements__r)
                       {
                               If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
                                       {
                                          
                                           count = JPInnerLoop.End_Date__c.daysBetween(JPOuterLoop.Start_Date__c);
                                           
                                             If ((count <=30 && count > 0) && !(JPId.contains(JPOuterLoop.Id)))
                                             { 
                                                JPId.add(JPOuterLoop.Id);
                                                finalList.add(JPOuterLoop);
                                             }
                                       }
                           
                        }
                  }
             }
        

All Answers

Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean,
I am not sure if you can do it in SOQL.  But you may create a formula field of Number data type to store the date difference and use that field in your soql to check if it is lesser than 30.
Let me know if you need more details.
 
Jean Grey 10Jean Grey 10
I need to compare dates across multiple Job Placement records when the related Contacts are the same. Example:

Contact 1
    Job Placement 1A
        Start Jan 1 2017
        End March 30 2017
    Job Placement 1B
        Start Apr 1 2017
        End May 1 2017
    Job Placement 1C
        Start Aug 1 2017
        End Dec 1 2017

Contact 2
    Job Placement 2A
        Start Jan 1 2017
        End Jan 30 2017
    Job Placement 2B
        Start Apr 1 2017
        End July 30 2017
    Job Placement 2C
        Start Aug 15 2017
        End Dec 31 2017

Query/logic should return Job Placements 1B and 2C.
 
Jean Grey 10Jean Grey 10
This is what I have so far.

//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);
        }
    }
 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean,
Try the below code and let me know 

Note: Please check the field name and table names before you proceed.

List<Contact> cont = [SELECT Id,Name,(SELECT ts2__Start_Date__c, Actual_End_Date__c  FROM ts2__Placements__c) FROM Contact];
List<ts2__Placement__c> finalList = new List<ts2__Placement__c>;
for(Contact c : cont)
     {
     for(ts2__Placement__c JPOuterLoop : c.ts2__Placements__c)
           {
            for(ts2__Placement__c JPInnerLoop : c.ts2__Placements__c)
               {
                       If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
                               {
                                     If (JPInnerLoop.Actual_End_Date__c.daysBetween(JPOuterLoop.ts2__Start_Date__c) <=30)
                                    finalList.add(JPOuterLoop);
                               }
                }
          }
     }
 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean,
Did this work for you?  
Jean Grey 10Jean Grey 10
No, the code threw an error, probably because placement is a many to one relationship (see error). 
Didn't understand relationship 'ts2__Placement__c' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean

If you have not found solution yet, please try this and let me know. I have tested this code and it is working fine
In this code, replace
Job_Placement with your table name
Start_Date with your exact field name start date
End_Date with your exact field name end date.
Dont change __c or __r


List<Contact> contList = [SELECT Id, Name,(SELECT ID, Name, Start_Date__c, End_Date__c FROM Job_Placements__r) FROM Contact];
        Integer count ;
        Set<Id> JPId = new set<Id>();
        List<Job_Placement__c> finalList = new List<Job_Placement__c>();
        for(Contact c : contList)
             {     for(Job_Placement__c JPOuterLoop : c.Job_Placements__r)
                   {
                    for(Job_Placement__c JPInnerLoop : c.Job_Placements__r)
                       {
                               If (JPOuterLoop.ID != JPInnerLoop.ID) // To make sure not to check with same end date
                                       {
                                          
                                           count = JPInnerLoop.End_Date__c.daysBetween(JPOuterLoop.Start_Date__c);
                                           
                                             If ((count <=30 && count > 0) && !(JPId.contains(JPOuterLoop.Id)))
                                             { 
                                                JPId.add(JPOuterLoop.Id);
                                                finalList.add(JPOuterLoop);
                                             }
                                       }
                           
                        }
                  }
             }
        
This was selected as the best answer
Jean Grey 10Jean Grey 10
I got an error: Line: 1, Column: 26
Didn't understand relationship 'Job_Placement__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean,
Have you put the custom child object name in plurals? If you make sure to put <object_name>s__r as in my code, this error should not come.
Please share your code if you get this error even after making the object name to plural name whereever applicable.

As a standard, we follow the naming convention of pluralizing the child object_name as the relationship name when querying from the parent object.

Hope this helps!
 
Jean Grey 10Jean Grey 10
Thanks you, this worked, but I believe I hit the governor limit:
Line: 1, Column: 1
System.LimitException: Too many query rows: 50001

Any suggestions to avoid this limit?
Malni Chandrasekaran 2Malni Chandrasekaran 2
Jean,
There are many ways to do it and it completely depends on your requirement and implementation.

You may use batch Apex.
Governor limits count for each Apex transaction. For Batch Apex, these limits are reset for each execution of a batch of records.

You may use Limit in your Query to check the functionality of this code and please mark it as solved.
And create a new question request in forum with your detailed requirement. Many forum members will provide you many suggestions as it is a very common issue that everyone face. Creating new request will put your question in top and will get focus of many forum members.

- Thanks.