If I may suggest...
1. We don't need RBAR to do this... no WHILE loops please.
2. We don't need 8 functions to do this... that's a lot of complicated code to maintain not to mention the overhead of calling 8 functions.
3. We certainly don't need any hardcoding of times for this. Even the DatePart(DW) shouldn't be hardcoded because of the possible settings of DATEFIRST
4. The absolute best way to do this is to have a Calendar table. Yeah, I know... for some reason, lots and lots of folks would rather use some very complicated code and While Loops to do this instead of the very simple method of using a Calendar table.
With all of that in mind, might I suggest the following instead?
--===== Declare some obviously named variables
DECLARE @StartDateTime DATETIME,
@Saturday INT, --Datepart(dw) for Saturday regardless of DATEFIRST
@Sunday INT --Datepart(dw) for Sunday regardless of DATEFIRST
--===== Preset the variables
SELECT @StartDateTime = '2007-11-16 15:30', --Likely parameter in function
@EndDateTime = '2007-11-20 14:00', --Could be parameter in function
@WorkTimeStart1 = '07:30', --Could be parameter in function
@WorkTimeEnd1 = '11:30', --Could be parameter in function
@WorkTimeStart2 = '12:00', --Could be parameter in function
@WorkTimeEnd2 = '16:00', --Could be parameter in function
@BinSize = 15, --Minutes, Could be parameter in function
@Saturday = DATEPART(dw,5), --First Saturday of 1900
@Sunday = DATEPART(dw,6) --First Sunday of 1900
--===== Using the start and end time, calculate the number of business hours
-- between those two date/times.
(--==== Produces a list of datetime slots in @BinSize minute intervals
SELECT DATEADD(mi,(t.n-1)*@BinSize,@StartDateTime) AS TimeSlot
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(mi,@StartDateTime,@EndDateTime)/@BinSize
(--==== Separates the Time as a separate column and removes weekends
Date = DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0),
Time = ts.TimeSlot - DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0)
FROM cteTimeSlots ts
WHERE DATEPART(dw,ts.TimeSlot) NOT IN (@Saturday,@Sunday)
--===== Counts time slots within the workday and converts to
-- decimal hours. To exclude dates from a Holiday table,
-- uncomment the last line and modify as necessary.
FROM cteDates d
(d.Time >= @WorkTimeStart1 AND d.Time < @WorkTimeEnd1)
(d.Time >= @WorkTimeStart2 AND d.Time < @WorkTimeEnd2)
-- AND NOT EXISTS (SELECT 1 FROM dbo.Holiday h WHERE d.Date = h.Date)
As usual, details of how it works are in the comments. If you don't know what a Tally table is or how it works, please see the following article...http://www.sqlservercentral.com/articles/T-SQL/62867/
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs