TimeSpan between multiple Days (without Weekend/Holidays)

Transact-SQL
1

We have a table with Service-Times (Time from to Time to AND a
TicketNr). These times can also go over several days (Example:
2017-02-03 15:00 - 2017-02-06 12:30).
The task is to calculate the
time difference in minutes, whereby no weekends (Sat, Su) OR holidays
may be considered. The information about Weekend or Holidays comes from a
Dim-Table (D_Time).
Which approach would you choose?
Thanks
Regards
Nicole

2

Something like this:

select 
	a.timeFrom,
	a.timeTo,
	datediff(mi,timeFrom,timeTo)-SUM(coalesce(case when HolidayDate is null then 0 else 1 end) * 1440)
from
	YourServiceTimesTable a
	cross join YourHolidayTable h
where
	a.timefrom <= h.HolidayDate
	and a.timeTo >= h.HolidayDate
group by
	a.timeFrom,
	a.timeTo