[SalesForce] Formula to put a date together seperatly Fields “Month” and “Year”

What if you created a Master Detail relationship between Service__c and a new object, Month__c. This new object has the Service__c field for it's parent record, and a Date field.

For every Service__c object, there exists a Month__c detail object for every month you want to count the Opportunity as "in service". Maybe the field should just be a string like '2012-12' instead of a Date datatype.

Use Batch Apex to create the initial Month__c records by iterating through all Service__c objects.

If a Service__c object is deleted for some reason, all of it's related Month__c records will go with it.

Add a trigger on Service__c to create Month__c objects for new Service__c objects or if either Contract_Start_Date__c or Date_to_Disconnect__c change.

Now you can create SOQL queries on Month__c to get the SUM of records for each month, and the total MRR. You could add one or more formula fields to represent whether the parent Service__c is a connect or disconnect in the given month, and your SOQL could include the sum of connects and disconnects.

For example, the detail records for a contract that starts in July 2012 and ends in November 2012 would look like this:

 Date      Connect  Disconnect
2012-07       1          0
2012-08       0          0
2012-09       0          0
2012-10       0          0
2012-11       0          1

It might not be the most elegant solution, since you end up with a lot of Month__c objects, but this seems like something that can't be easily calculated in a SOQL query. After the initial setup, it should be easy to maintain the Month__c objects using triggers on Service__c.

You can create formula fields as follows

Month

MONTH(CloseDate)

Year

YEAR(CloseDate)

Quarter

FLOOR((MONTH(CloseDate)-1)/3) + 1