+ Start a Discussion
Tatu LaaksoTatu Laakso 

AggregateResult WEEK_IN_YEAR changes week number between wednesday and thursday, why?

Hi All

I am new to the apex and trying to link Sobjects Task Activities to a Sobject Weekly Utilization with the same week number and summarize activities' durations. I made a trigger which uses aggregateresult to do this, and it works just fine except it thinks, that the same week's wednesday and thursday have different week numbers. Could anyone help me with this?

Code:
trigger WeeklyUtilizationTrigger on Weekly_Utilization__c (after insert, after update) {

	if((!UtilizerClass.inUtiSync) && (!UtilizerClass.inTaskSync)){
        
        UtilizerClass.inUtiSync = true;

        Map<Id,SFDC_Resource__c> resot = new Map<Id,SFDC_Resource__c>();
        
        Map<Id,Task_Activity__c> tasksToUpdate = new Map<Id,Task_Activity__c>();
        
        List<Weekly_Utilization__c> newUtiz = new List<Weekly_Utilization__c>();
        
        List<Task_Activity__c> tsksToUpdate = new List<Task_Activity__c>();
        
        List<Weekly_Utilization__c> utizToUpdate = new List<Weekly_Utilization__c>();
        
        Map<Id,Task_Activity__c> acts = new Map<Id,Task_Activity__c>();
        
        List<Date> dlist = new List<Date>();

        Set<Id> tskIds = new Set<Id>();

        Set<Id> utiIds = new Set<Id>();
        
        Set<Id> resIds = new Set<Id>();
        
        Map<Id,Task_Activity__c> taskIdsToTask = new Map<Id,Task_Activity__c>();
        
        Map<Id,Id> wuResIds = new Map<Id,Id>();
        
        Map<Id,Weekly_Utilization__c> utiz = new Map<Id,Weekly_Utilization__c>(); 
        
        for(Weekly_Utilization__c uti : Trigger.New){
            dlist.add(uti.Start_Date__c);
            resIDs.add(uti.Resource__c);
            utiz.put(uti.Id,uti);
            resot.put(uti.Resource__c,null);
            wuResIDs.put(uti.Id,uti.Resource__c);
            utiIds.add(uti.Id);
            
        }
        //Collecting dates and using the smallest one as a filter
        dlist.sort();
        Date PVM_A = dlist[0];

        //Quering Task Activities

        for(Task_Activity__c tsk :[select Id, Resourcee__c, Billable__c, Project_Task__c, Duration_in_minutes__c, Start__c, End__c, Week_Number__c 
                                    from Task_Activity__c 
                                    where Resourcee__c in :resIds
                                    and End__c>=:PVM_A
                                    and End__c <:PVM_A+7
                                    and Billable__c=true]){
            acts.put(tsk.Id,tsk);
            taskIdsToTask.put(tsk.Id,tsk);
        }
        system.debug('taskIdsToTask values: '+taskIdsToTask.keySet());
        for(Task_Activity__c tsk :[select Id, Resourcee__c, Billable__c, Project_Task__c, Duration_in_minutes__c, Start__c, End__c, Week_Number__c 
                                    from Task_Activity__c 
                                    where Resourcee__c in :resIds
                                    and End__c>=:PVM_A
                                    and End__c <:PVM_A+7
                                    and Weekly_Utilization__c in :utiIds]){
            tskIds.add(tsk.Id);
        }
        Map<String,List<Id>> tskDates = new Map<String,List<Id>>();
        Map<Id,Map<String,Decimal>> durations = new Map<Id,Map<String,Decimal>>();
        
        //AggregateResult for Task Activities
        for(AggregateResult ar : [select Id, CALENDAR_YEAR(End__c) year, WEEK_IN_YEAR(End__c) week, SUM(Duration_in_minutes__c) duration
                                    from Task_Activity__c 
                                    where End__c>=:PVM_A
                                    and End__c <:PVM_A+7
                                    and Billable__c=true
                                    and Resourcee__c in :resIds
                                    group by rollup (CALENDAR_YEAR(End__c), WEEK_IN_YEAR(End__c), Id)]){
                                        


            if(ar.get('year') != null && ar.get('week') != null && ar.get('Id') != null){

                Task_Activity__c tsk = taskIdsToTask.get((Id)ar.get('Id'));
                if(tsk!=null){
                    system.debug('AGGREGATE RESULT TASK ID: '+(Id)ar.get('Id'));
                    system.debug('TSK RECORD: '+tsk);
                    system.debug('TSK RECORD WEEK: '+String.valueOf(ar.get('week')));
                    system.debug('TSK RECORD WEEK: '+(Long)ar.get('week'));
                    String yearAndWeek = String.valueOf(ar.get('year')) + '-' + String.valueOf(ar.get('week'));
                    
                    if(tskDates.containsKey(yearAndWeek + '-' + tsk.Resourcee__c)){

                        tskDates.get(yearAndWeek + '-' + tsk.Resourcee__c).add(tsk.Id);

                    }else{
                        tskDates.put(yearAndWeek + '-' + tsk.Resourcee__c, new List<Id>{tsk.Id});
                    }
                    
                    if(durations.containsKey(tsk.Resourcee__c)){
                        if(durations.get(tsk.Resourcee__c).containsKey(yearAndWeek)){
                            durations.get(tsk.Resourcee__c).put(yearAndWeek,durations.get(tsk.Resourcee__c).get(yearAndWeek) + (Decimal)ar.get('duration'));
                        }else{
                            durations.get(tsk.Resourcee__c).put(yearAndWeek,(Decimal)ar.get('duration'));
                        }
                    }else{  
                        durations.put(tsk.Resourcee__c, new Map<String,Decimal>{String.valueOf(ar.get('year'))+'-'+String.valueOf(ar.get('week')) => (Decimal)ar.get('duration')});
                        
                    }
                }
            }
                                        
            
                                   
        }
        //AggrefateResult for Weekly Utilization
        system.debug('TASKDATES: '+tskDates);
        for(AggregateResult ar : [select Id, CALENDAR_YEAR(Start_Date__c) year, WEEK_IN_YEAR(Start_Date__c) week, SUM(Duration_in_minutes__c) duration
                                    from Weekly_Utilization__c
                                    where Id in :Trigger.New
                                    group by rollup (CALENDAR_YEAR(Start_Date__c), WEEK_IN_YEAR(Start_Date__c), Id)]){
                                        
            Id resourceId = wuResIds.get((Id)ar.get('Id'));
            String yearAndWeek = String.valueOf(ar.get('year')) + '-' + String.valueOf(ar.get('week'));
                                        
            if(durations.containsKey(resourceId)){
                if(durations.get(resourceId).containsKey(yearAndWeek)){ 
                    utizToUpdate.add(new Weekly_Utilization__c(
                                        Id=(Id)ar.get('Id'),
                                        Duration_in_minutes__c = (Decimal)durations.get(resourceId).get(yearAndWeek)
                                        
                    ));
                }
            }
            if(ar.get('year') != null && ar.get('week') != null && ar.get('Id') != null){
                    
                    if(tskDates.containsKey(yearAndWeek+'-'+resourceId)){

                        for(Id i2 : tskDates.get(yearAndWeek+'-'+resourceId)){
                            
                            system.debug('TASK TO UPDATE: '+i2);     
                            tsksToUpdate.add(new Task_Activity__c(
                                                                    Id=i2,
                                                                    Weekly_Utilization__c=(Id)ar.get('Id')
                                        ));
                            
                        }
                        
                    }
                             
                
            }

            
        } 
        for(Task_Activity__c tsk :[select Id from Task_Activity__c where Id in :tskIds and Id not in :tsksToUpdate]){
            
                    system.debug('Task To Update: '+tsk);
                    tsksToUpdate.add(new Task_Activity__c(
                                        Id=tsk.Id,
                                        Weekly_Utilization__c=null
                                        ));
                
            
        }
                                        
          system.debug('Tasks To Update Size: '+tsksToUpdate.size());


        update  utizToUpdate;
        update tsksToUpdate;
    }
}
Thanks
Tatu
 
Eric PepinEric Pepin
I don't think it uses the calendar (Sunday-Saturday) to determine week number. I believe that WEEK_IN_YEAR reports the week # by taking the number of days elapsed in the year and dividing that by 7. So January 1-7 will always be Week 1, January 8-14 will always be Week 2, etc. Regardless of the day of the week that they fall on.