You may well have this solved by now, but I thought I'd chime in and provide the connection between a query to figure out the total opening hours and your actual Operations table. I'm taking a liberty here and just assuming that the Operations table has a primary key field, and just going with that and the start and end date fields, and then just using a generated calendar table to provide a structure for expanding out all possible dates and times of day, by the hour, and then just counting how many fall into the range. The dates CTE is good for about 3 years worth, so if you need more, you can always add an additional reference to the TALLY cte. Take a look-see and let me know if this helps:

CREATE TABLE #OpeningHours (

WorkingDay VARCHAR(12),

from_time TIME,

to_time TIME,

DayNumber tinyint

);

INSERT INTO #OpeningHours (WorkingDay, from_time, to_time, DayNumber)

VALUES ('Monday', '08:00:00.000', '22:00:00.000', 2),

('Tuesday', '08:00:00.000', '22:00:00.000', 3),

('Wednesday', '08:00:00.000', '22:00:00.000', 4),

('Thursday', '08:00:00.000', '22:00:00.000', 5),

('Friday', '08:00:00.000', '22:00:00.000', 6),

('Saturday', '08:00:00.000', '18:00:00.000', 7),

('Sunday', '08:00:00.000', '18:00:00.000', 1);

CREATE TABLE #Operations (

PRIM_KEY int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

[start_date] datetime,

[end_date] datetime

);

INSERT INTO #Operations ([start_date], [end_date])

VALUES ('2016-11-01', '2016-11-17');

--DETERMINE THE MIN AND MAX FOR ALL POSSIBLE DATES AND THE NUMBER OF DAYS

DECLARE @NUM_DAYS AS bigint;

DECLARE @START_DATE AS datetime;

SELECT @NUM_DAYS =

DATEDIFF(day, MIN([start_date]), MAX([end_date])) + 1,

@START_DATE = MIN([start_date])

FROM #Operations;

WITH TALLY AS (

SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

ALL_TIMES AS (

--THIS QUERY JUST GENERATES ALL 24 HOURS IN A DAY AS TIME VALUES

SELECT TOP (24)

CAST(

DATEADD(hour,

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, 0)

AS time) AS THE_TIME

FROM TALLY AS T1, TALLY AS T2

),

ALL_DATES AS (

--THIS QUERY JUST GENERATES ALL THE DATES BETWEEN THE MIN AND MAX

SELECT TOP (@NUM_DAYS)

DATEADD(day,

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1,

@START_DATE) AS THE_DATE,

DATEPART(weekday,

DATEADD(day,

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1,

@START_DATE)

) AS DAY_NUMBER

FROM TALLY AS T1, TALLY AS T2, TALLY AS T3

)

SELECT O.PRIM_KEY, O.[start_date], O.[end_date], COUNT(T.THE_TIME) AS TOTAL_OPEN_HOURS

FROM #Operations AS O

INNER JOIN ALL_DATES AS D

ON D.THE_DATE BETWEEN O.[start_date] AND O.[end_date]

INNER JOIN #OpeningHours AS H

ON D.DAY_NUMBER = H.DayNumber

INNER JOIN ALL_TIMES AS T

ON T.THE_TIME >= H.from_time

AND T.THE_TIME < H.to_time

GROUP BY O.PRIM_KEY, O.[start_date], O.[end_date]

ORDER BY O.PRIM_KEY;

DROP TABLE #OpeningHours;

DROP TABLE #Operations;