divide the time per month

A-T · ‎2022-11-21

Hello all,

Thanks for the effort, i need support to calculate the time "difference between 2 dates" and divide the result per month

for example

ticket "reported date" 18-Jul-22 04:37 PM

Closed"WO_Act finish" 05-Sep-22 12:00 PM

the result should be 48.8 days = 1171.38Hrs to close

Now i need to divide this value so that, each month present the corresponding time spent

meaning that we spend total (1171.35hrs "319.4 Hrs in july-22" and "744Hrs in august-22" and "108hrs in September-22"

Thanks in advance

AT

MarcoWedel · ‎2022-11-21

Hi,

one example that might work for you:

table1:
LOAD RecNo() as ID,
     Timestamp#(Reported_Date,'DD-MMM-YY hh:mm TT')	as Reported_Date,
     Timestamp#(WO_ACTFINISH,'DD-MMM-YY hh:mm TT')	as WO_ACTFINISH
Inline [
    Reported_Date, WO_ACTFINISH
    18-Jul-22 04:37 PM, 05-Sep-22 12:00 PM
    19-Jul-22 05:43 AM, 21-Aug-22 12:00 PM
    02-Aug-22 12:34 PM, 22-Sep-22 02:14 PM
    12-Aug-22 11:11 AM, 22-Aug-22 11:11 PM
    13-Aug-22 10:01 AM, 22-Dec-22 01:10 PM
];

table2:
LOAD *,
     Interval(To-From,'hh:mm') as Duration;
LOAD ID,
     MonthName(Reported_Date,IterNo()-1) as MonthYear,
     Timestamp(RangeMax(Reported_Date,MonthStart(Reported_Date,IterNo()-1)),'DD-MMM-YY hh:mm TT')	as From,
     Timestamp(RangeMin(WO_ACTFINISH, MonthStart(Reported_Date,IterNo()  )),'DD-MMM-YY hh:mm TT')	as To
Resident table1
While MonthStart(Reported_Date,IterNo()-1) <= WO_ACTFINISH;

A-T · ‎2022-11-22

Hello marco,

thanks for the great support i've tried the example looks good if I have a limited data "rows" unfortunately is not the case

more explanation of what i am looking for

Reported date: indicate the time ticket created
Ticked has different status (New/in progress/ closed)
WO_ Actual finish: always empty unless the ticket is closed

now i need to calculate the time spent (TTC) to close the ticket

If the ticket closed then TTC= WO Act finish - Reported date

if the ticket in new/progress and since the WO act finish empty then TTC =end of month - reported date

and if we move to the next month and ticket still open then TTC will keep counting from the beginning of next month until we have time in WO act finish then TTC will equal WO Act finish-reported date

TTC result should be divided per month/week "reporting period" as sometime the ticket opened 28th of the month and closed 5th of next month so I need to show that I've spent 24 in this month, 36 hrs. next month and so on

Thanks for the great support hopefully this clarifies more.

BR,

AT

Scripting