+ Start a Discussion
Luke Higgins 22Luke Higgins 22 

Add values from a parent-child SOQL query

I am trying to get the sum of a result from a parent-child query and insert the sum into a field on the parent. The object placement__c has a child record timesheet__c with the field, hours__c, for each week. I am trying to get the total of approved hours for the last 2 weeks on the placement__c record through a batch class. I can get the timesheet__c records grouped with this SOQL query of the placements__c, but I'm not sure how to get to the final product of adding them.

SELECT Id, Name, (SELECT jstcl__Total_Hours__c FROM jstcl__Timesheets__r WHERE jstcl__Week_Ending__c = LAST_N_DAYS:15 AND jstcl__Status__c = 'Approved') FROM ts2__Placement__c
Satya Prakash ChoudharySatya Prakash Choudhary
You can write nested for loops to go throug records.
 
List<ts2__Placement__c> placementlist = new List<ts2__Placement__c>([SELECT Id, Name,    (SELECT jstcl__Total_Hours__c FROM jstcl__Timesheets__r WHERE jstcl__Week_Ending__c = LAST_N_DAYS:15 AND jstcl__Status__c = 'Approved') FROM ts2__Placement__c]);

Integer totalPlacementHour;

for(ts2__Placement__c placement: placementlist ){

Integer totalPlacementHour = 0;
      for(jstcl__Timesheets__c timesheet:placement.jstcl__Timesheets__r ){

            totalPlacementHour = totalPlacementHour + jstcl__Total_Hours__c;
       }


}

 
Luke Higgins 22Luke Higgins 22
Hey Satya,

Thanks for your response! The code you gave me is giving me trouble on inserting it in into a batchable class. I'm trying to set it up in the same way as another batch class I've written but it doesn't seem to comply with the outline. The totalPlacementHour needs to go in the Hours_Worked_the_Past_2_weeks__c field on the placement. Here's what I've been trying to do: 
public class hfFlagB implements Database.Batchable<sObject>, Database.Stateful{

    List<ts2__Placement__c> query = [SELECT Id, Name,    (SELECT jstcl__Total_Hours__c FROM jstcl__Timesheets__r WHERE jstcl__Week_Ending__c = LAST_N_DAYS:15 AND jstcl__Status__c = 'Approved') FROM ts2__Placement__c WHERE VMS__c = '' AND ts2__Status__c ='Active' AND (Market__c = 'Milwaukee' OR Market__c = 'Minneapolis') FOR UPDATE];
    Integer totalPlacementHour = 0;

    public Database.QueryLocator start(Database.BatchableContext bc) {
      for(ts2__Placement__c placement: query ){
          for(jstcl__Timesheets__c timesheet:placement.jstcl__Timesheets__r ){
             totalPlacementHour = totalPlacementHour + jstcl__Total_Hours__c;
       }
      }
      return Database.getQueryLocator(' ');  // not sure what to query here
      }
     public void execute(Database.BatchableContext BC, List<jstcl__TG_Timesheet__c> a){
     for(ts2__Placement__c  b : a){
       b.Hours_Worked_the_Past_2_weeks__c = totalPlacementHour;
       }
       update a;
       }
     public void finish(Database.BatchableContext BC){}
}

Do you have any idea how to help?

Thanks again!