+ Start a Discussion
DodiDodi 

Select Distict Month ?

I am trying to return the disctinct number of months in the last 24 months that we have an invoice line item record. The below query returns me the disctinct number of InvoiceDate values, but I just need the disctinct months(not individul dates). So my count should never be over 24. Any suggestions?

 

AggregateResult[] activePeriodCount = [SELECT account__c aid, COUNT_DISTINCT(InvoiceDate__c) ap FROM InvoiceLineItem__c where Account__c IN :setAccountId AND InvoiceDate__c != NULL AND Revenue != NULL
                                        AND Revenue!= 0 AND InvoiceDate__c =  LAST_N_YEARS:2  GROUP BY Account__c];

 

Another option is to do 24 queries with the invdividual months applied to the query, but I am trying to avoid that.

 

Thanks
       

Best Answer chosen by Admin (Salesforce Developers) 
Vinit_KumarVinit_Kumar

The way I would approach is by creating a Formula field which would only return the month for Date field InvoiceDate__c and then I would use that field in my SOQL query to get the distinct months values.

All Answers

Vinit_KumarVinit_Kumar

The way I would approach is by creating a Formula field which would only return the month for Date field InvoiceDate__c and then I would use that field in my SOQL query to get the distinct months values.

This was selected as the best answer
DodiDodi

Thanks Vinit, I have not found a clean quick way to do this in Apex, so I will do your suggestion.

 

Regards,

 

Dodi