Is there a way to get the total time for multiple tickets in a report?

In some of our workflows we have a start and end time (Type: Date/Time). I am looking for a way to find the total time for multiple tickets in a report. 

So for example in a report I get 3 Tickets that shows outage time with different start and end times. I want to get the total time for each outage ticket and add them together and multiply the number of minutes for that month. To see the percentage of time down time for that month.

Thanks for the help in advance. 

Parents
  • 0

    I'm guessing that since the start/end times are for an "outage" that you're using Calendar 0 i.e. 24x7. What happens if the Start date/time is in Oct and the End date/time is in Nov?  Which month would you use to calculate percentage?

    I'd suggest looking at Query View Advanced Reports, aka XML ReportsBut first!!! Make sure that you or your SQL guru can create a query that will generate what you need for each ticket (elapsed time of ticket; first and last days of the month for the ticket's End Date; Sum of elapsed times for all tickets where End Date is between the first and last days calculated).  SBM QVAR (XML) reports look like a aux table.  They are essentially the SQL you provide running as a subquery.  This has big implications for report performance and what your SQL can get away with.

  • 0 in reply to 

    I was able to create a Query View Advanced Report aka Advanced XML Report that shows the Elapsed Time for one of our Cases from SUBMITDATE to CLOSE_DATE plus the that Elapsed time as a percentage of all Cases that were closed in the same month.  Let me know if you want me to post it here.

    It's kind of a work-in-progress because our Cases can take many days and we might get 100 new Cases a day.  This causes integer numeric overflows because the total ET in seconds for all Cases closed in same month is more than SQL Server MAXINT, and SBM XML reports for SBM 11.8 don't support "numeric-integer64" field types .... AFAICT.  I'm experimenting with doing the Elapsed Time calculations in Minutes or Hours.

Reply
  • 0 in reply to 

    I was able to create a Query View Advanced Report aka Advanced XML Report that shows the Elapsed Time for one of our Cases from SUBMITDATE to CLOSE_DATE plus the that Elapsed time as a percentage of all Cases that were closed in the same month.  Let me know if you want me to post it here.

    It's kind of a work-in-progress because our Cases can take many days and we might get 100 new Cases a day.  This causes integer numeric overflows because the total ET in seconds for all Cases closed in same month is more than SQL Server MAXINT, and SBM XML reports for SBM 11.8 don't support "numeric-integer64" field types .... AFAICT.  I'm experimenting with doing the Elapsed Time calculations in Minutes or Hours.

Children
No Data