sql - Calculating processing time based on business hours instead of total hours

Question

I'm trying to calculate the service hours that have passed since a ticket has been logged. When a ticket gets logged a timestamp is saved with it (date_logged). When it gets closed another timestamp is saved (date_closed).

Needed

What I need is the hours that have passed between the date_logged and the current datetime (for open tickets) or the hours between the date_logged and the date_closed (for closed tickets) based on the service hours of the department assigned to the ticket.

Public holidays have to be included.

Existing Table

The service hours of the assigned department are saved in the same table as the ticket. The table looks something like this:

incident_ref	department	date_logged	date_closed	sla_mon_start	sla_mon_end	sla_tue_start	sla_tue_end	sla_wed_start	sla_wed_end	sla_thr_start	sla_thr_end	sla_fri_start	sla_fri_end	sla_sat_start	sla_sat_end	sla_sun_start	sla_sun_end
1660565	A	06.01.21 11:30:52		01.01.01 07:30:00	01.01.01 16:45:00	01.01.01 07:30:00	01.01.01 16:45	01.01.01 07:30:00	01.01.01 16:45:00	01.01.01 07:30:00	01.01.01 16:45:00	01.01.01 07:30:00	01.01.01 13:00:00	01.01.01 00:00:00	01.01.01 00:00:00	01.01.01 00:00:00	01.01.01 00:00:00
1660567	B	13.01.21 09:14:16		01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 15:00:00	01.01.01 00:00:00	01.01.01 00:00:00
1660558	C	31.12.20 07:04:46	31.12.20 07:36:59	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 16:30:00	01.01.01 07:00:00	01.01.01 15:00:00	01.01.01 00:00:00	01.01.01 00:00:00
3456789	D	01.01.21 09:41:00	04.01.21 08:21:00	01.01.01 08:00:00	01.01.01 15:00:00	01.01.01 08:00:00	01.01.01 15:00:00	01.01.01 08:00:00	01.01.01 15:00:00	01.01.01 08:00:00	01.01.01 15:00:00	01.01.01 08:00:00	01.01.01 13:00:00	01.01.01 00:00:00	01.01.01 00:00:00	01.01.01 00:00:00	01.01.01 00:00:00
0123456	D	02.01.21 13:12:00	...	...	...	...	...

question from:https://stackoverflow.com/questions/66059336/calculating-processing-time-based-on-business-hours-instead-of-total-hours

Answer 1

Waitting for answers