StartTime                EndTime                   Process                   
-----------------------  ------------------------ -----------------------
1900-01-01 09:00:00.000  1900-01-01 17:00:00.000   Email and Faxing
1900-01-01 07:30:00.000  1900-01-01 17:00:00.000   Scanning and Indexing

ALTER FUNCTION fn_LengthOfWorkingDay
(
@process VARCHAR(25)
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @returnDate DATETIME
	SELECT @returnDate = EndTime - StartTime FROM WorkingDayHours WHERE Process = @process
	RETURN @returnDate
END

1900-01-01 09:30:00.000

CREATE FUNCTION fn_CalculateDateDifference
(
  @startDate DATETIME
, @endDate  DATETIME
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @returnDate DATETIME
	SELECT @returnDate = @endDate - @startDate
	RETURN @returnDate
END

CREATE FUNCTION fn_CalculateMSBetweenDates
(
  @startDate DATETIME
, @endDate  DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @milliseconds INT
	SELECT @milliseconds = (SELECT DATEDIFF(MS,@startDate, @endDate))
	RETURN @milliseconds
END

rls_end_time              Scan Start time           Time Difference                                        
------------------------ ------------------------- ------------------------
2005-09-08 09:50:50.000  2005-09-08 09:44:51.000   1900-01-01 00:05:59.000
2005-09-13 15:38:59.000  2005-09-09 09:52:53.000   1900-01-05 05:46:06.000
2005-09-09 11:13:29.000  2005-09-09 10:51:13.000   1900-01-01 00:22:16.000
2005-09-14 10:53:44.000  2005-09-13 07:58:02.000   1900-01-02 02:55:42.000

CREATE FUNCTION fn_CalculateWorkingHours
(
  @startDate DATETIME
, @endDate DATETIME
, @process VARCHAR(25)
)
RETURNS BIGINT
AS
BEGIN
	DECLARE @milliseconds BIGINT
	DECLARE @numOfFullDays TINYINT
	DECLARE @timePortion DATETIME
	
	SET @numOfFullDays = 0
	
IF (DATEDIFF(DAY,@startDate,@endDate) > 0)
	BEGIN

		--Calculate the time lapsed between the start of the process to the end of the working day
		SELECT @timePortion = @startDate - CAST(FLOOR(CAST(@startDate AS FLOAT))AS SMALLDATETIME)
		SELECT @milliseconds = DATEDIFF(ms,@timePortion, WorkingDayHours.EndTime) FROM WorkingDayHours
	
		--Calculate the time lapsed between the start of the working day and the end of the process
		SELECT @timePortion = @endDate - CAST(FLOOR(CAST(@endDate AS FLOAT))AS SMALLDATETIME)
		SELECT @milliseconds = @milliseconds + DATEDIFF(ms, WorkingDayHours.StartTime, @timePortion) 
					FROM WorkingDayHours
					WHERE Process = @process
	
		--Add a day onto the start date as you do not want to include the first day in the  full working day count
		SELECT @startDate = DATEADD(DAY,1,@startDate)

		--Eradicate the time portion of the dates
		SELECT @startDate = CAST(FLOOR(CAST(@startDate AS FLOAT))AS SMALLDATETIME)
		SELECT @endDate = CAST(FLOOR(CAST(@endDate AS FLOAT))AS SMALLDATETIME)
	
			--Calculate the number of full working days that have lapsed between the start and end date of the process
			--As long as the day is less then (not <=) the last day date will not be included in the full working day count 
		WHILE @startDate < @endDate
			BEGIN
				--If the date is not included in the dates held in the BankHoliday Table, then it can be included in the count
				IF NOT EXISTS(SELECT BankHolidayDate FROM BankHoliday WHERE BankHolidayDate = @startDate)
					BEGIN
						--If the day falls on a weekend, then it is not included in the count
						SELECT @numOfFullDays = @numOfFullDays + 
									(CASE DATENAME(DAY,@startDate)
									WHEN 'Saturday' THEN 0
									WHEN 'Sunday' THEN 0
									ELSE 1
									END)
					END
				SELECT @startDate = DATEADD(DAY, 1, @startDate)
	
			END
	
		--Add the number of milliseconds for the working days to the total
		SELECT @milliseconds = @milliseconds + (@numOfFullDays * dbo.fn_CalculateMSBetweenDates(StartTime, EndTime))
					FROM WorkingDayHours WHERE Process = @process
	END
ELSE 
	BEGIN
	SELECT @milliseconds = DATEDIFF(MS,@startDate,@endDate)
	END

	RETURN (@milliseconds)
END