Help with date range

[Anonymous]

Hi! 

I have this Ticket_history table: 

ID	TicketID	Updated	status
1	59	2020-04-12 15:00	opened
2	59	2020-04-12 16:00	work
3	59	2020-04-12 17:00	waiting for customer
4	59	2020-04-12 18:00	work
5	59	2020-04-12 19:30	waiting for customer
6	59	2020-04-12 20:00	closed

I need to be able to get the amount of hours this ticket has been open and subtract the amount of hours it has been waiting for customer.

We are waiting for customer 17 -> 80 and 19:30 -> 20 = 1.5 hours
Result TicketTable:

ID	hours open	hours waiting for customer
59	5	1.5

[FrankAT · Accepted solution]

Hi @Anonymous ,

the following solution works if each TicketID

• has one open and one closed status
• has only two waiting for customer status, the first followed by work status and the other by closed status.

Source data is:

Solution:

Regards FrankAT

[parry2k]

@Anonymous how did you get to waiting hours 80 min and 20 min = 100 min (1.5 hours)?