How to calculate hours for ea month when enddate spans multiple months

[CCH50]

I have created a report that returns workorders for the last year from current date, it is grouped by location, equipment and month. I need to provide the hours and equipment availibility by month. The Workorder may have multiple actions, an action can start/end in same month or span months. I need to calculate and display the hours for each month. as an example,

A workorder starts on 16 jul 2010, the technician creates the 1st action event and works on it until 23 jul 2010, another technician then works on it from the 23 Jul 2010 until 13 Aug 2010, still another technician works on it from the 13 Aug 2010 to 05 Sep 2010 and lastly another technician works on it from the 05 Sep 2010 to the 10 Dec 2010.

I need to calculate the hrs the workorder was active for each month.(The Dates used are fields in database)
so for Jul , datediff( "h",#2010/07/16#),#2010/07/23#),+ datediff( "h",#2010/07/23#),#2010/07/31#)
for aug datediff( "h",#2010/08/01#),#2010/08/13#), datediff( "h",#2010/08/13#),#2010/08/31#),
for sep datediff( "h",#2010/09/01#),#2010/09/05#), datediff( "h",#2010/09/05#),#2010/09/31#),
for oct
for nov
for dec

so the problem is because the records are grouped by month and the workorder hours span multiple months how do I account for the hours in the groups of Oct, Nov, Dec.
CR 2008 Oracle

 

[Madawc]

Create a Formula Field that checks the end date. If it is not in the same month as the start date, then
a) If the current month/ year is not the same as the end date, then use the last date in the month. (You can get this easily by specifying the first day in the next month and subtracting one day.)
b) If it is in the month, use that dte.

You'll probably also have to do something equivalent for start date.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[CCH50]

I've tried this but I don't seem to get the correct hrs.

If mth=emth then //If EOM in July
T_JulHrs:= datediff("n", {WFR_ESR_ACTIONS.START_DT},{WFR_ESR_ACTIONS.STOP_DT})/60
else if emth = mth + 1 Then// If EOM in Aug
(
T_JulHrs:= t_julHrs + datediff("n", {WFR_ESR_ACTIONS.START_DT},Date(y,mth,31))/60; //Hrs to EOM
T_Aughrs:= datediff("n", date(y,emth,01),{WFR_ESR_ACTIONS.STOP_DT})/60;
T_Aughrs:= T_AugHrs + datediff("n", {WFR_ESR_ACTIONS.START_DT},{WFR_ESR_ACTIONS.STOP_DT})/60 //Hrs to close
)

 

[Madawc]

I'd suggest breaking the command up into several separate items, isolate whatever the problem is.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP