• I don't know Luis, I think that's not complicated enough 🙂

    A quick example building some tables to work with. It was thrown together quickly so some testing may be in order and the Calendar and TimeOfDay tables are not all they could be as of yet

    --First, lets make a Calendar table

    -- Note: usually, this would have some more fields so it could be used for other things

    CREATE TABLE [dbo].[Calendar](

    [calendar_date] [date] NOT NULL,

    [month_nbr] [tinyint] NOT NULL,

    [month_name] [varchar](10) NOT NULL,

    [day_of_week_nbr] [tinyint] NOT NULL,

    [day_of_week_name] [varchar](10) NOT NULL,

    [year_nbr] [int] NOT NULL,

    [IsWeekend] [bit] NOT NULL,

    [IsHoliday] [bit] NOT NULL,

    CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED

    (

    [calendar_date] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    -- Now, a TimeOfDay table (same note as above)

    CREATE TABLE [dbo].[TimeOfDay](

    [TimeOfDay] [time](0) NOT NULL,

    [hour_nbr] [tinyint] NOT NULL,

    [minute_nbr] [tinyint] NOT NULL,

    CONSTRAINT [PK_TimeOfDay] PRIMARY KEY CLUSTERED

    (

    [TimeOfDay] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    -- Now we create a ScheduleTimes table. If this was a working solution, you would have a parent Schedule table too describing each schedule, but I skipped it

    -- this table lets you customize all those business rules

    CREATE TABLE [dbo].[ScheduleTimes](

    [time_id] [int] IDENTITY(1,1) NOT NULL,

    [schedule_id] [int] NOT NULL,

    [start_time] [time](0) NOT NULL,

    [end_time] [time](0) NOT NULL,

    [time_description] [varchar](255) NULL,

    [in_working_day] [bit] NOT NULL,

    [is_working_time] [bit] NOT NULL,

    CONSTRAINT [PK_ScheduleTimes] PRIMARY KEY CLUSTERED

    (

    [time_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    -- Now lets load the Calendar table with some test dates. For now I only make christmas day a holiday. Make your own holiday rules or even set them as you do with your holiday table

    ;

    WITH t AS (

    SELECT 1 as mynum 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),

    quicktally AS (

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

    FROM t t1, t t2, t t3

    ),

    dates AS (

    SELECT DATEADD(day, qt.rn, CAST(getdate() AS DATE)) datetally

    FROM quicktally qt

    )

    INSERT Calendar (calendar_date, month_nbr, month_name, day_of_week_nbr, day_of_week_name, year_nbr, IsWeekend, IsHoliday)

    SELECT d.datetally,DATEPART(month,d.datetally),DATENAME(month,d.datetally),DATEPART(weekday,d.datetally),DATENAME(weekday,d.datetally),DATEPART(year,d.datetally),CASE WHEN DATENAME(weekday,d.datetally) IN ('Saturday','Sunday') THEN 1 ELSE 0 END, CASE WHEN MONTH(d.datetally)=12 AND DAY(d.datetally)=25 THEN 1 ELSE 0 END

    FROM dates d

    -- Now, fill up TimeOfDay

    ;

    WITH t AS (

    SELECT 1 as mynum 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),

    quicktally AS (

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

    FROM t t1, t t2, t t3, t t4

    ),

    times AS (

    SELECT CAST (DATEADD(MINUTE, qt.rn, CAST(CAST (getdate() AS DATE) AS DATETIME)) AS TIME(0)) timetally

    FROM quicktally qt

    )

    INSERT TimeOfDay (TimeOfDay, hour_nbr, minute_nbr)

    SELECT TOP 1440 ti.timetally, DATEPART(HOUR,ti.timetally),DATEPART(minute,ti.timetally)

    FROM times ti

    -- And now add times for the schedule your function used

    INSERT [dbo].[ScheduleTimes] (schedule_id, start_time, end_time, time_description, in_working_day, is_working_time)

    SELECT 1, '00:00:00','07:29:59','Morning pre-work',0,0

    UNION ALL

    SELECT 1, '07:30:00', '09:44:59','Morning pre-break',1,1

    UNION ALL

    SELECT 1, '09:45:00', '09:59:59','Morning break',1,0

    UNION ALL

    SELECT 1, '10:00:00', '12:29:59','Morning post break',1,1

    UNION ALL

    SELECT 1, '12:30:00', '12:59:59','Lunch',1,0

    UNION ALL

    SELECT 1, '13:00:00', '15:59:59','Afternoon work',1,1

    UNION ALL

    SELECT 1, '16:00:00', '23:59:59','Afternoon post work',0,0

    -- Now, we define our function. Inline table valued as with Luis'

    CREATE FUNCTION dbo.SetBasedWorkingMinutes (@StartDate datetime, @EndDate datetime, @schedule_id int)

    RETURNS TABLE

    AS

    RETURN (

    SELECT SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.in_working_day = 1 THEN 1 ELSE 0 END) AS WorkingDayMinutes,

    SUM(CASE WHEN c.IsWeekend = 0 AND c.IsHoliday = 0 AND s.is_working_time = 1 THEN 1 ELSE 0 END) AS WorkedMinutes,

    FROM ( SELECT CAST(c.calendar_date AS datetime) + CAST(t.TimeOfDay AS datetime) DateAndTime,c.calendar_date ,t.TimeOfDay,c.IsWeekend,c.IsHoliday

    FROM Calendar c

    CROSS JOIN TimeOfDay t

    ) c

    JOIN ScheduleTimes s

    ON c.TimeOfDay BETWEEN s.start_time AND s.end_time

    WHERE c.DateAndTime >= @StartDate

    AND c.DateAndTime < @EndDate

    AND s.schedule_id = @schedule_id

    )

    GO

    NOTES:

    -The function added a parameter to link to the schedule times table (so you could create a 9-5 schedule, for example, and run the function against it)

    -The function is returning minutes, not hours

    -Since the function is table valued for performance, I gave you 2 return fields, WorkingDayMinutes counts the minutes from the start to end time without worrying about breaks, WorkedMinutes should match what you had in your query)

    - This example took a while to put together even using quickly thrown together data, so it should be tested carefully before being used

    - Because it actually selects instead of parsing date/times, the wider your date range, the worse it will perform compared to Luis' . But it should still be pretty fast (2 years took me 1 second) as long as the size of the calendar table is reasonable.

    - The ScheduleTimes table lets you customize business rules around breaks and such, and use the same function for different working schedules

    - It would actually perform better I think with one date/time calendar table with a record per minute (make better use of an index), but I like the tables separate as both have other uses

    EDIT: Fixed the end time to match the test case. Had 5pm by accident originally. Damn 24 hour clock 🙂