+ Start a Discussion
Charline MitchenerCharline Mitchener 

SOQL Query to aggregare records based on date field

The contacts in our org are required to complete a minimum number of CPD points each year. From Jan 1 - Dec 31.
Previously we have been doing this manually but want to now have a way to record this in Salesforce. 

So far, we have created a CPD Register object, where our contacts can enter their Activities and points gained for that activity.
The fields on this object include;
- Activity Date
- Activity Type
- Lookup to contact
- Lookup to Event Registrations (a seperare object where we have all event registrations for events hosted by us)
- CPD Points gained for activity

We have also created a new custom object called Total CPD Points, where we need to calculate the total cpd points for a contact WHERE the activity date is between Jan 1 to Dec 31 for a particular year.

We need to be able to report by year, e.g. One record will equal one year, and the declarative roll up summary in the record will display total points for that year. 

I am trying trying to work out the best way to go about this, and am looking for suggestions. Currently looking at declarative roll up summary option to have on the TOTAL CPD Points object. 

So far I have come up with two options... but wanted to see if anyone else had any other ideas.

1.  Activity_Date__c = THIS_YEAR
2. Or creating a start date field, and an enda date field, then having a soql 
WHERE createdDate >= startdate field
AND createdDate <= end date field