How to count an average date?

Question

My accounts have the Date field Activity__c. How can I figure out an average date between all these Activity__c fields and define the closest date to the average date?

I tried to use SOQL: [SELECT AVG(Activity__c) FROM Account WHERE Activity__c != null]

But it shows an error: field Activity__c does not support aggregate operator AVG.

Now I know that aggregate function average doesn't work with Date. Could somebody help me, please?

Answer 1

I did it:

//method finds closest date to average date

private static Date findClosestDateToAvg(){
    List<Date> dates = new List<Date>();
    for (Account acc : [SELECT Activity__c
                        FROM Account
                        WHERE Activity__c != NULL]) {
        dates.add(acc.Activity__c);
    }
    
    Date avgDate = calcAvgDate();
    Date closestDate = findMinDate();
    for (Date d : dates) {
        if (Math.abs(d.daysBetween(avgDate)) < Math.abs(closestDate.daysBetween(avgDate))) {
            closestDate = d;
        }
    }
    return closestDate;
}

// method calculates average date in fileds Activity__c of Account

private static Date calcAvgDate() {
    Date minDate = findMinDate();
    Date maxDate = findMaxDate();
    Integer diff = minDate.daysBetween(maxDate);
    return minDate.addDays(diff/2);
}

private static Date findMinDate(){
    return (Date)[SELECT MIN(Activity__c)minDate FROM Account][0].get('minDate');
}

private static Date findMaxDate(){
    return (Date)[SELECT MAX(Activity__c)maxDate FROM Account][0].get('maxDate');
}