Business Time in Seconds Function

create function dbo.f_business_seconds (@startdate datetime, @enddate datetime, @starthour datetime, @endhour datetime, @workdays varchar(7))
Returns BigInt as

BEGIN

/****************************************************************************************************************************
* SQL Server Function to determine number of business seconds between two datetime fields.
*
***************************************************************************************************************************** 
* Parameters: 
*@startdate - Start Time (datatype DateTime)
*@enddate - End Time (datatype DateTime)
*@starthour - Start of Business Day (datatype DateTime) (Format: '1900-01-01 9:00:00')
*@endhour - End of Business Day (datatype DateTime) (Format: '1900-01-01 17:00:00')
*@workdays - Business Day string - Use Dash ('-') for Non-Business Day (datatype char(7)) (Format: '-MTWTF-')
*
* Example: 
*     dbo.f_business_seconds('2005-01-10 9:00:00','2005-01-16 18:00:00','1900-01-01 9:00:00','1900-01-01 17:00:00','-MTWTF-')
*
* Returns: BigInt value that represents time in seconds
*
*****************************************************************************************************************************/
/****************************************************
* Initialize Variables
*****************************************************/
DECLARE @workstartdate datetime, @workenddate datetime, @workstarttime datetime, @workendtime datetime, @weekendflag bit, @bussecs bigint

set @workstartdate = convert(varchar,@startdate,101)
set @workenddate = convert(varchar,@enddate,101)
set @workstarttime = convert(varchar,@startdate,108)
set @workendtime = convert(varchar,@enddate,108)
set @weekendflag = 0
set @bussecs = 0

/********************************************************************************
* If Start Date is on Non-Workday then rollback start date to last workday
*********************************************************************************/
while substring(@workdays,datepart(dw,@workstartdate),1) = '-'
begin
set @weekendflag = 1 
set @workstartdate = dateadd(day,-1,@workstartdate)
end

/*******************************************************************************
* If Start Date is on Non-Workday then set start time to end of last work day
********************************************************************************/
if @weekendflag = 1
begin
set @workstarttime = @endhour
set @weekendflag = 0
end

/******************************************************************************
* If End Date is on Non-Workday then rollback end date to last workday
*******************************************************************************/
while substring(@workdays,datepart(dw,@workenddate),1) = '-'
begin
set @weekendflag = 1 
set @workenddate = dateadd(day,-1,@workenddate)
end

/*****************************************************************************
* If End Date is on Non-Workday then set end time to end of last work day
******************************************************************************/
if @weekendflag = 1
begin
set @workendtime = @endhour
set @weekendflag = 0
end

/****************************************************************************
* Adjust Start Time and End Time if outside business hours
*****************************************************************************/
if @workstarttime < @starthour
set @workstarttime = @starthour

if @workendtime < @starthour
set @workendtime = @starthour

if @workstarttime > @endhour
set @workstarttime = @endhour

if @workendtime > @endhour
set @workendtime = @endhour

/**************************************************************************
* Calculate business hours if Start Date and End Date are the same
***************************************************************************/
if @workstartdate = @workenddate
set @bussecs = datediff(second,(@workstartdate + @workstarttime),(@workenddate  + @workendtime))

/**************************************************************************
* Calculate business hours if Start Date and End Date are the different 
* but no full business days in between.
***************************************************************************/
else
begin
if @workstartdate = dateadd(day,-1,@workenddate)
set @bussecs = datediff(second,@workstarttime,@endhour) + datediff(second,@starthour,@workendtime)

/*************************************************************************
* Calculate business hours if Start Date and End Date are the different 
* and have one or more full business days in between.
**************************************************************************/
else
begin
while @workstartdate < @workenddate
begin
set @workstartdate = dateadd(day,1,@workstartdate)

if substring(@workdays,datepart(dw,@workstartdate),1) <> '-'
set @bussecs = @bussecs + datediff(second,@starthour,@endhour)
end

set @bussecs = @bussecs - datediff(second,@starthour,@endhour)
set @bussecs = @bussecs + datediff(second,@workstarttime,@endhour) + datediff(second,@starthour,@workendtime)

end
end


/**************************************************
* Return Number of Business Seconds
**************************************************/
return @bussecs
END