3

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

1 Answer 1

2

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .