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 🙂