T-SQL Function to calculate net business hours
Posted on 2007-12-04
Need to create a scalar function in T-SQL that is compitble with SQL Server 2000. Function should return an INT value expressing the number of elapsed time during normal business hours,
specifically, the number of elapsed seconds between two times, optionally excluding weekends and overnight hours.
Should have these input parameters:
@StartTime .... the starting time being measured
@EndTime.....the ending time being measured
@StartOfBusnDay..... the start of business day (for example, 9:00AM)
@EndOfBusnDay.....The End Of Business Day (For example 5:00PM)
@IncludeSat... Boolean value ; If YES then treat with same rules as regular business days, else skip it
@IncludeSun...Boolean value, If YES then treat with same rules as regular business days, else skip it
example: NetBusinessDays('11/19/2007 7:11AM','11/30/2007 3:35PM','9:00AM','5:00PM',false,false)
Also include inside the code (does not need input parameters) a hard-coded list of holidays that should always be skipped over: for example IF BusinessDay in ('11/20/2007','12/25/2007','1/1/2008')....