+ Start a Discussion
SATHISH REDDY.SATHISH REDDY. 

OpportunityLineItemSchedules group by Date in SOQL

Hi all,
I have a list of products with associated schedules where each schedule has different Revenue and ScheduleDate values. I'm trying to get the get the aggregate of Revenue by DATE across the OpportunityLineItems. However, i'm not sure how to achieve this as i couldn't find any date functions specific to date while DAY_ONLY() will only work for datetime fields. Please share your thoughts & recommendations.
Thanks!
Abdul KhatriAbdul Khatri
Hi Sathish,

Can you provide some schema, field level details for more clarity?
 
SATHISH REDDY.SATHISH REDDY.
Hi Abdul,

I have an opportunity which has - lets say 6 products (prod count varies by oppty) with associated schedules. I'm trying to insert a new product(Name - AGGREGATE PRODUCT), whose schedules must show the aggregate of all 6 product's schedules by "ScheduleDate".

I dont want to FOR loop over all the schedules of those 6 products & create a map with Map<Scheduledate, Sumof(revenue)> which would consume more cputime causing exception. Thought if there is any aggregateResult that can be used to get the sumof Revenue by Scheduledate.

Let me know if that makes sense.

Thanks!
Abdul KhatriAbdul Khatri
Hi Sathish

Youi can try something like this
 
SELECT SUM(Revenue), Scheduledate FROM OpportunityLineItem
WHERE OpportunityId = <Id>
GROUP BY Scheduledate

 
SATHISH REDDY.SATHISH REDDY.
The query you shared is for OpportunityLineItem but i need to aggregate the "Revenue" from OpportunityLineItemSchedules. The above query doesn't work as OpportunityLineItem obj doesnt have Revenue,Scheduledate fields.
Abdul KhatriAbdul Khatri
Sorry made there a mistake of OpportunityLineItemShedule. Changing that will fulfill expectation?
SATHISH REDDY.SATHISH REDDY.
No, it did not.