Solved

T-SQL Function to calculate net business hours

Posted on 2007-12-04
6
4,591 Views
Last Modified: 2012-08-13
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')....
0
Comment
Question by:Louis Capece
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
OK, first you'll need a table of sequential numbers from 0 (*zero*) to the max number of days that will be in one date range.  I'll assume the table is named SeqNums and the column is SeqNum.
CREATE FUNCTION dbo.NetBusinessDays (

    @StartTime DATETIME,

    @EndTime DATETIME,

    @StartOfBusnDay DATETIME,

    @EndOfBusnDay DATETIME,

    @IncludeSat BIT,

    @IncludeSun BIT

)

RETURNS INT

AS

BEGIN
 

DECLARE @totalSeconds INT

SET @totalSeconds = 0
 

SELECT @totalSeconds = @totalSeconds + CASE 

    WHEN (DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @StartTime)) = 'Saturday' AND @IncludeSat = 0)

      OR (DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @StartTime)) = 'Sunday' AND @IncludeSun = 0)

      OR (DATEADD(DAY, seqNum, CONVERT(CHAR(8), @StartTime, 112)) IN --list of holidays follows

         ('11/20/2007','12/25/2007','01/01/2008'))

    THEN 0

    ELSE CASE 

         WHEN seqNum = 0 THEN CASE  --first(/only) day

             WHEN CONVERT(CHAR(5), @StartTime, 108) >= CONVERT(CHAR(5), @EndOfBusnDay, 108) 

             THEN 0

             ELSE DATEDIFF(SECOND, CASE WHEN CONVERT(CHAR(5), @StartTime, 108) < CONVERT(CHAR(5), @StartOfBusnDay, 108)

                                        THEN @StartOfBusnDay ELSE CONVERT(CHAR(5), @StartTime, 108) END,

                                   CASE WHEN CONVERT(CHAR(5), @EndTime, 108) > CONVERT(CHAR(5), @EndOfBusnDay, 108)

                                          OR DATEDIFF(DAY, @StartTime, @EndTime) > 0

                                        THEN @EndOfBusnDay ELSE CONVERT(CHAR(5), @EndTime, 108) END)

             END

         WHEN seqNum = DATEDIFF(DAY, @StartTime, @EndTime) THEN CASE  --last day

             WHEN CONVERT(CHAR(5), @EndTime, 108) < CONVERT(CHAR(5), @StartOfBusnDay, 108) 

             THEN 0

             ELSE DATEDIFF(SECOND, @StartOfBusnDay,

                                   CASE WHEN CONVERT(CHAR(5), @EndTime, 108) > CONVERT(CHAR(5), @EndOfBusnDay, 108)

                                        THEN @EndOfBusnDay ELSE CONVERT(CHAR(5), @EndTime, 108) END)

             END

         ELSE DATEDIFF(SECOND, @StartOfBusnDay, @EndOfBusnDay)  --in-between day(s)

         END

    END

FROM seqNums

WHERE seqNum BETWEEN 0 AND DATEDIFF(DAY, @StartTime, @EndTime)
 

RETURN @TotalSeconds
 

END --FUNCTION

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Code below will create a sample seqnums table from 0-9999 (about 27 yrs' worth of days).

Note that the unique clustered index is **very important for performance**.
CREATE TABLE seqNums (

    seqNum SMALLINT,

    CONSTRAINT seqNums_CI

        UNIQUE CLUSTERED (seqNum)

    )

INSERT INTO seqNums

SELECT DISTINCT ones + tens + hundreds + thousands

FROM (

    SELECT 0 AS ones UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 

    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 

    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

) AS ones

CROSS JOIN (

    SELECT 00 AS tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL 

    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL 

    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90

) AS tens

CROSS JOIN (

    SELECT 000 AS hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL 

    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL 

    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900

) AS hundreds

CROSS JOIN (

    SELECT 0000 AS thousands UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL 

    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL 

    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000

) AS thousands

ORDER BY ones + tens + hundreds + thousands
 

SELECT COUNT(*) FROM seqNums

Open in new window

0
 

Author Closing Comment

by:Louis Capece
Comment Utility
Scott - You are the Man!
Thanks for VERY QUICK response. I have been searching high and low for a solution like this... I would imagine this solution can help a lot of people, it is a very flexible approach.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Thanks for the kind comments.  While I believe the code is accurate, naturally please be sure to extensively test the function before relying on its results :-) .
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
Very nice indeed Scott - seqNums strikes again.... :)
0
 

Expert Comment

by:mdawson_cf
Comment Utility
A friend of mine needed something similar for minutes.  This has no holiday check, but wouldn't be hard to add in, thought I'd throw this out there.  We only had an hour to do it, this seemed to work.

CREATE FUNCTION [dbo].[getBusinessMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS bigint
AS


BEGIN
declare @StartDate datetime
declare @EndDate datetime
declare @BizStart datetime
declare @BizEnd datetime
declare @StartTime datetime
declare @EndTime datetime
declare @StartDateBizEndDiff datetime
declare @EndDateBizStartDiff datetime
declare @MinDiff bigint

--business rules
set @BizStart = '1/1/1900 7:00:00' --7am
set @BizEnd = '1/1/1900 19:00:00' --7pm

--get the hours remaining in first day
set @StartTime = CASE WHEN cast('1/1/1900 ' + convert(varchar,@StartDate,114) as datetime) <= @BizStart
                              THEN @BizStart
                              ELSE cast('1/1/1900 ' + convert(varchar,@StartDate,114) as datetime)
                              END
set @StartDateBizEndDiff = @BizEnd - @StartTime

--get the hours into the last day
set @EndTime = CASE WHEN cast('1/1/1900 ' + convert(varchar,@EndDate,114) as datetime) >= @BizEnd
                        THEN @BizEnd
                        ELSE cast('1/1/1900 ' + convert(varchar,@EndDate,114) as datetime)
                        END
set @EndDateBizStartDiff = @EndTime - @BizStart

--gets minutes diff on start and end days
set @MinDiff = (datepart(hh,@StartDateBizEndDiff) * 60) + datepart(mi,@StartDateBizEndDiff)
+ (datepart(hh,@EndDateBizStartDiff) * 60) + datepart(mi,@EndDateBizStartDiff)

--get weekdays
declare @WkDayCt int
declare @LoopDate datetime
set @WkDayCt = 0
set @LoopDate = cast(convert(varchar,@StartDate,101) as datetime)

while @LoopDate < cast(convert(varchar,@EndDate,101) as datetime)
begin
      if cast(datepart(dw,@LoopDate) as int) between 2 and 6
            begin
                  set @WkDayCt = @WkDayCt + 1
            end
      set @LoopDate = @LoopDate + 1
end

set @MinDiff = @MinDiff + ((@WkDayCt - 1) * 12 * 60)
return @MinDiff
END
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now