I work as jr system administrator in a small outsourcing company where we offer IT services to other companies. We use osTicket as a Tickets Management System but reports and exports in this are quite weird and not really clever. I manage to make KPIs for my IT team and once a month I need to export all tickets from osTicket web platform and organize them in an Excel worksheet where I add all needed infos such as the number of tickets opened per category, per operator, per company etc.
In order to automate the process, I used Metabase to read the osTicket DB, I just miss the last information to have it perfectly work as I would: the processing time intercurring between a ticket is opened and closed.
The "easy" calculation is quite simple:
ROUND(SUM(TIMESTAMPDIFF(SECOND, ost_ticket.created, ost_ticket.closed)/3600)) AS 'Processing Time'
The problem is that this simple operation calculates the total time in hours (with 2 decimals) between opened and closed: as you can imagine, to correctly analyze SLAs the query should take into account that we don't work 24/7 but only weekdays from 09:00 am to 1:00 pm and from 2:00 pm to 6:00 pm.
I tried to read tons of forums, even asking to ChatGPT but it's only a month that I started writing queries and this just isn't up to me. Could you help me to better understand the logic I should use to achieve the result?
I tried the following:
TIMESTAMPDIFF(
MINUTE
,CASE WHEN DAYOFWEEK(ost_ticket.created) = 6
THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 18 HOUR) -- Friday
WHEN DAYOFWEEK(ost_ticket.created) = 7
THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 10 HOUR) -- Saturday
ELSE ost_ticket.created
END
,CASE WHEN DAYOFWEEK(ost_ticket.closed) = 6
THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 10 HOUR) -- Saturday
WHEN DAYOFWEEK(ost_ticket.closed) = 7
THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 18 HOUR) -- Sunday
ELSE ost_ticket.closed
END
) / 60 AS 'Tempo di evasione'
as suggested by ChatGPT but it's too complicated and the result obtained in minutes seems incorrect.
I like to tackle these problems in stages. The solution may not be the most elegant or efficient, but it works.
First, let's get some data we can work with:
Now, what we need to know is where the timespans overlap the shifts. I took the approach of finding all the shifts that overlap the timespan, adjusting the first and last shift and then adding them together. The first thing to do is find all the days in the time span. We can do that with the following recursive CTE:
Code Segment 1
For the sample data set, this produces:
From there, we can create rows for each shift by cross joining
ALL_DAYSwith a time table. This stage can also eliminate those weekends. So the second CTE is:Code Segment 2
Which produces:
Now it's time to pull in the cases where start and end times are inside the shifts. Note that we also want to eliminate any situations that don't make sense. This CTE does both steps:
Code Segment 4
Producing:
And with that, all that is left is to calculated the date differences and sum the hours.
Code Segment 4
Which, for my sample set, produces these results:
To run the actual solution you have to combine all four code segments into one query. You will also need to adjust field and table names to suit your unique situation.