Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

i need help for my SOQL. i have employee custom object with this information Employee table Employee table

i want to select the employee information about total employee per department per month to date, for example for this year January total employee for cloud dept is 1, project is 1, MS is 1 and sales dept with 0 empl. see this picture

enter image description here

is it possible to get return from SOQL like that?

Regards,

Willy

share|improve this question

1 Answer 1

You can't do this through regular SOQL, as you can't group by a start and end date range.

If you want to accomplish this through a SOQL query on demand, you'll need an additional field in your custom object that contains the month (or maybe month/year, depending on whether you want Jan 2014 to be different to Jan 2013). While at first glance a formula field seems the best solution, you can't group by formula fields so you have to use a text field and populate this via workflow or a trigger. Once you have the field populated (I'm assuming its called MonYearStr__c) you can query back the totals and group by date and department, e.g.

List<AggregateResult> ars=[select count(id), MonYear__c,Department__c 
                  from MyCustom__c group by MonYear__c, Department__c];

This will return a count of records for each unique Month/Year and department combination. You can then process the results and output your table.

If you don't want to add extra fields, you'll need to pull back all of the records and manually group them by date and department yourself - if there are a large number of records this might cause a problem with governor limits.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.