Solved

T-SQL Function to calculate net business hours

Posted on 2007-12-04
6
4,623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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:
Scott Pletcher 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 69

Expert Comment

by:Scott Pletcher
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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