[SalesForce] Calendar Week Date Range as a formula field

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.

TEXT(MONTH(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ ' to ' + 
TEXT(MONTH((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))

which on today's date (02/14/2013), would return:

2/10/2013 to 2/17/2013

I utilized the formula provided in this post to get the current offset on the day of the week. Using that, I just subtracted from today. Unfortunately, you wanted the date formatted outside of the base format of a date which would be 2013-02-14. To take account for that, I had to do some manipulation and just pull in the month, day, and year of the date.