We have an object called "Compensation__c". This object contains the compensation a partner receives for selling a "Product2".
"Compensation__c" has a start date. Before this date this compensation is not valid yet. A "Product2" can have multiple compensation records scheduled in the future, so today I will get €1,- and I might get €2,- tomorrow for this same product and €1,50 next week.
I need to create a SOQL query which returns the current compensation record for a selection of products.
SELECT Id, Product__c, PartnerCompensation__c FROM Compensation__c WHERE CompensationType__c = :compensation AND StartDate__c <= TODAY AND Product__c IN :productIds
How do I select the most recent active compensation? I want to group by Product__c, but then all other fields must be aggregated as well. I do not want an average or max compensation, I want the actual compensation...