I've been asked to make a sql server query where I need to remove the Overlap time period between the service tickets. Like we have a service which is facing 3 different issues at a similar time. The name of the table is ticket and it has n-number of tickets in it, out of which I am interested in these 3 since they belong to the same service.
Ticket Number Start Time End Time
1 11:00 15:00
2 12:00 13:00
3 14:00 16:00
I needed a query to calculate the total downtime during these tickets were active, which is 5 hours (11:00-16:00).
What to do ?
You can use DATEDIFF and SUM functions together:
SELECT DATEDIFF(HOUR, MIN([Start Time]),MAX([End Time]))AS [total downtime]
FROM tblTicketTable
WHERE [Ticket Number] = 1 OR [Ticket Number] = 2 OR [Ticket Number] = 3