Solved

T-SQL Function to calculate net business hours

Posted on 2007-12-04
6
4,598 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
ID: 20406366
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
ID: 20406405
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
ID: 31412646
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20406953
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
ID: 20434987
Very nice indeed Scott - seqNums strikes again.... :)
0
 

Expert Comment

by:mdawson_cf
ID: 24470579
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 50
Get Duration of last Status Update 4 31
Sql Count with Select Distinct 4 27
ms sql last 8 weeks as columns 5 28
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

920 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

17 Experts available now in Live!

Get 1:1 Help Now