[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

T-SQL Function to calculate net business hours

Posted on 2007-12-04
6
Medium Priority
?
4,651 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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 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