[SalesForce] Case Report grouped by Hour of Day

So you could do something like this:

Decimal MilliSeconds = EndTime.getTime() -StartTime.getTime() ; Decimal HourConvert = Millisecds / (1000.0*60.0*60.0)

When you use .getTime() it returns the number of Milliseconds since 1970.

Subtract the two to get the total milliseconds between the two dateTimes.

Then you take the milliseconds to calculate up to get your hours. 1000 (Seconds), * 60 (Minutes) * 60 (hours)

Edit Based On actually reading the questions correctly*

So the above gives you total time.

To get the Night/Day hours you need to break it up.

1. Figure out Hours of Day and Night for the Start Time
2. Figure out Hours of Day and Night for End Time
3. Figure out Total WHOLE DAYS between Start and End time. Then you can split that number to get your Day/Night Hours.

So that's the basis of this. Below is a class (Quick and Dirty) to do just what I listed above. What's not included is the Rounding up of the values.

public class TimeCalc {

Public static string TimeCalcHr(DateTime InputStart, Datetime InputEnd){
    Datetime DayStart = datetime.newinstance(InputStart.Year(),InputStart.Month(), Inputstart.Day(),6,0,0);
    Datetime DayEnd = datetime.newinstance(InputStart.Year(),InputStart.Month(), Inputstart.Day(),12,0,0);

    Decimal StartingDaysMS = InputStart.getTime() - DayStart.getTime();
    Decimal StartingNightsMS = InputStart.getTime() - DayEnd.getTime();

    if(StartingDaysMS <0){
       StartingDaysMS =0;
    }
    if(StartingNightsMS <0){
        StartingNIghtsMS = 0;
    }
    //Repeat for End Date
    DayStart = datetime.newinstance(InputEnd.Year(),InputEnd.Month(), InputEnd.Day(),6,0,0);
    DayEnd = datetime.newinstance(InputEnd.Year(),InputEnd.Month(), InputEnd.Day(),12,0,0);

    Decimal EndingDaysMS = InputEnd.getTime() - DayStart.getTime();
    Decimal EndingNightsMS = InputEnd.getTime() - DayEnd.getTime();
    if(EndingDaysMS <0){
       EndingDaysMS =0;
    }
    if(EndingNightsMS <0){
        EndingNIghtsMS = 0;
    }
    //Figure out hours between
    decimal NightMSInADay = DayEnd.GetTime() - DayStart.getTime();
    Decimal DayMSInADay = DayStart.Adddays(1).getTime()-DayEnd.GetTime();
    //figure out number of days between times
    integer NumOfDays = InputStart.Date().daysBetween(InputEnd.Date()); 
    Decimal NumMSBetweenDays = NumOfDays * DayMSInADay;
    Decimal NumMSBetweenNights = NumOfDays * NightMSInADay;

    //Add Everything Togeter
    Decimal TotalNightMS = NumMSBetweenNights +  EndingNIghtsMS + StartingNightsMS;
    Decimal TotalDaysMS = NumMSBetweenDays+ EndingDaysMS+ StartingDaysMS;
    Decimal TotalNightHrs = TotalNightMs / (1000*60*60);
    Decimal TotalDayHrs = TotalDaysMS /  (1000*60*60);

    String Output = 'Night Hours: ' + string.Valueof(TotalNighthrs) + ' Day Hours: ' + string.valueof(TotalDayHrs);
    return Output;
}

}

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.