Calculating Work Days

  • I got it, I missed out OUTPUT when declare @workdays as INT. Thanks

  • There is a problem with the function and it is basically a problem with counting days. To illustrate, let's use something that is not a date. Say we have two bags of marbles, one with six marbles and one with ten marbles. We can ask, "What is the difference in the number of marbles between the two bags?" The answer is four. We can say that the smaller bag has four fewer marbles or that the larger bag has four more marbles but the difference between them is four. What about two bags with ten marbles each? The answer is zero. There is no difference. If we add a marble to one of the bags, the answer becomes one. And so forth.

    Actually, this is a way to check that we have the right answer. We start out with two bags of six marbles each. What is the difference? Zero. We add one marble to a bag and add one to the answer. What is the difference now? One. We add another marble to the same bag and add one more to the answer. We continue until the bag has ten marbles. We see that the answer has been incremented to four so we have verified the answer to the first question above.

    Now let's do the same thing with dates. Let's start out with the same date, say '12/24/2007' and '12/24/2007' (which happens to be last Monday). What is the difference (in days) between them? There is no difference between them, they are the same date. So the answer is zero. We can use DateDiff as a check: select DateDiff(d, '12/24/2007', '12/24/2007') returns a zero, just as we expect. If we add one day to one of the dates then we also add one to our answer. So the number of days difference between '12/24/2007' and '12/25/2007' we can expect to be one. A quick call to DateDiff verifies that we are correct.

    Let's say we are measuring the number of work/week/business days between two events. The first event is when a certain process starts and the second event is when the process completes. However, we know that the process always starts on a Monday and always completes on or before the following Friday. With these constraints, we don't need to worry about crossing over weekends so we don't need a specific "workday only" calculator -- DateDiff should work just fine for this application. And indeed it does. Given a start date of '12/24/2007' and a complete date of '12/27/2007', we get back an answer of three.

    However, the function fn_WorkDays returns an answer of four for those dates. As a matter of fact, when we test fn_WorkDays against DateDiff, for dates that do not span a weekend, there is always one too many days. The same date, where we expect no difference, returns one. That means there is one day somewhere between '12/24/2007' and '12/24/2007' and two days between '12/24/2007' and '12/25/2007'.

    Why do I go to such length to make this point? Because I have had this discussion before. Apparently there is only one day between Monday and Tuesday when considering them as ordinary days but there are two days between Monday and Tuesday when considering them as Work Days. Why this is so has never been adequately explained to me but I have been told that this is how most companies count work days. None that I have worked for, mind you, but with less than a dozen employers under my belt, that is too few to detect an industry-wide trend. However, I sincerely hope this is not true -- that companies do not tack on an extra day when working with work days rather than ordinary calendar days. That would make working with work days (nice poetic phrase if you say it out loud: "working with work days") a good order of magnitude more difficult than it already is.

    So what is the answer? How many days are between two adjacent work days: one or two? How many days are between a work day and itself: zero or one? We can't write or use a "work day calculator" until we answer those questions.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • Overall a really good function but I hate error codes coming back from a function and our environment wanted us to return negative numbers when the start date was greater than the end date....so I added a few checks for the datetime parms and a third parameter...

    USE [MASTER]

    GO

    /****** Object: UserDefinedFunction [dbo].[FUNC_WorkDays] Script Date: 07/10/2008 13:38:05 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter FUNCTION [dbo].[fn_WorkDays]

    /***************************************************************************************

    Purpose:

    1. Given any valid start date and end date, this function will calculate and return

    the number of workdays (Mon - Fri).

    2. Given only a valid start date (end date has DEFAULT in it), this function will

    return a 1 if the start date is a weekday and a 0 if not a weekday.

    Usage:

    1. dbo.fn_WorkDays(@StartDate,@EndDate,'')

    2. dbo.fn_WorkDays(@StartDate,DEFAULT,'') --Always returns 1 or 0

    3. dbo.fn_WorkDays(@EndDate,@StartDate,'')

    4. dbo.fn_WorkDays(@StartDate,@StartDate,'') --Always returns 1 or 0

    5. dbo.fn_WorkDays(@EndDate,@EndDate,'') --Always returns 1 or 0

    6. dbo.fn_WorkDays(@StartDate,@EndDate,'Y') - if startdate < enddate result will be negative

    Notes:

    1. Holidays are NOT considered.

    2. Because of the way SQL Server calculates weeks and named days of the week, no

    special consideration for the value of DATEFIRST is given. In other words, it

    doesn't matter what DATEFIRST is set to for this function.

    3. If the input dates are in the incorrect order, they will be reversed prior to any

    calculations.

    4. Only whole days are considered. Times are NOT used.

    5. The number of workdays INCLUDES both dates

    6. Inputs may be literal representations of dates, datetime datatypes, numbers that

    represent the number of days since 1/1/1900 00:00:00.000, or anything else that can

    be implicitly converted to or already is a datetime datatype.

    7. Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the

    transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts

    only whole weekends in any given date range.

    8. This UDF does NOT create a tally table or sequence table to operate. Not only is

    it set based, it is truly "tableless".

    Error Indications:

    1. If either the @StartDate or the @EndDate parameter is an invalid date, the

    following error is returned...

    "Server: Msg 242, Level 16, State 3, Line 3

    The conversion of a char data type to a datetime data type resulted in an

    out-of-range datetime value."

    2. If either the @StartDate or the @EndDate parameter is a string not resembling a

    date, the following error is returned...

    "Server: Msg 241, Level 16, State 1, Line 3

    Syntax error converting datetime from character string."

    3. If only one parameter is passed, the following error is returned...

    "Server: Msg 313, Level 16, State 2, Line 3

    An insufficient number of arguments were supplied for the procedure or

    function MASTER.dbo.fn_WorkDays."

    Revisions:

    Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.

    Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.

    Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and

    modify to be insensitive to DATEFIRST settings.

    ***************************************************************************************/

    --======================================================================================

    -- Presets

    --======================================================================================

    --===== Define the input parameters (ok if reversed by mistake)

    (

    @StartDateX VARCHAR(22),

    @EndDateX VARCHAR(22) = NULL, --@EndDate replaced by @StartDate when DEFAULTed

    @Swapit char(1) = NULL -- if not null then don't swap

    )

    --===== Define the output data type

    RETURNS INT

    AS

    --======================================================================================

    -- Calculate the RETURN of the function

    --======================================================================================

    BEGIN

    --===== Declare local variables

    --Temporarily holds @EndDate during date reversal

    DECLARE @Swap DATETIME

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    DECLARE @Reverser int

    --===== RAR If StartDate or EndDate is not valid date, return NULL

    IF ISDATE(@StartDateX) = 0

    Select @StartDate = NULL

    IF ISDATE(@StartDateX) = 1

    Select @StartDate = @StartDateX

    IF ISDATE(@EndDateX) = 0

    Select @EndDate = NULL

    IF ISDATE(@EndDateX) = 1

    Select @EndDate = @EndDateX

    --===== If the Start Date is null, return a NULL and exit

    IF @StartDate IS NULL

    RETURN NULL

    --===== If the End Date is null, populate with Start Date value

    -- so will have two dates (required by DATEDIFF below)

    IF @EndDate IS NULL

    SELECT @EndDate = @StartDate

    --===== Strip the time element from both dates (just to be safe) by converting

    -- to whole days and back to a date. Usually faster than CONVERT.

    -- 0 is a date (01/01/1900 00:00:00.000)

    SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),

    @EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)

    --===== RAR set the reverser to negative or positive

    SELECT @Reverser = 1

    IF @Swapit = 'Y' and @StartDate > @EndDate

    Select @Reverser = -1

    --===== If the inputs are in the wrong order, reverse them

    IF @StartDate > @EndDate

    SELECT @Swap = @EndDate,

    @EndDate = @StartDate,

    @StartDate = @Swap

    --===== Calculate and return the number of workdays using the

    -- input parameters. This is the meat of the function.

    -- This is really just one formula with a couple of parts

    -- that are listed on separate lines for documentation

    -- purposes.

    RETURN (

    SELECT

    --Start with total number of days including weekends

    (DATEDIFF(dd,@StartDate,@EndDate)+1)

    --Subtact 2 days for each full weekend

    -(DATEDIFF(wk,@StartDate,@EndDate)*2)

    --If StartDate is a Sunday, Subtract 1

    -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'

    THEN 1

    ELSE 0

    END)

    --If EndDate is a Saturday, Subtract 1

    -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'

    THEN 1

    ELSE 0

    END)

    ) * @Reverser

    END

    Rick

    do it right, or do it over and over, it's up to you

  • ok i know this is a serious newb question and someone else had an issue when they tried to run this as an sp. i'm trying to run this as a function...created the function by copy pasting entire script posted on article..

    all i get when i call the function is command completed successfully. just doesnt return a value. am i missing something here?

    DECLARE @startdateDATETIME

    DECLARE @enddateDATETIME

    SET @startdate = '07/14/2008' --TESTING

    SET @enddate = '07/7/2008'

    exec fn_WorkDays @startdate,@enddate

    help!

  • dulanjan (9/8/2008)


    exec fn_WorkDays @startdate,@enddate

    help!

    Use exec to call a stored procedure. When calling a function, you have to capture the returned value for further processing and/or display.

    declare @WorkDayCount int;

    set @WorkDayCount = dbo.fn_WorkDays( @Startdate, @Enddate );

    select @WorkDayCount as ResultOfFunctionCall;

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • ohhh i see. wow. thanks a lot. never dawned on me.

    thank you very much.

  • is there anyway to edit this function so that it will return a value without declaring an output parameter?

    like how datediff or other functions work? simply two parameters and enclosed in parameters and it gets that value?

  • Use it any way you would use any function:

    if DateDiff( dd, @Startdate, @EndDate ) > dbo.fn_Workdays( @Startdate, @EndDate ) begin

    -- There is at least one weekend between the dates

    exec DoSomethingAboutIt...

    end;

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • excellent! i was trying it without the dbo part, trying to use it as a system function i guess 🙂

    you're the man!

  • jeff...i came up with an interesting dilemma with your function.

    if the start date is a saturday and the end date is a sunday it returns a -1.

    i think this should just return 0.

    no?

  • dulanjan (9/12/2008)


    jeff...i came up with an interesting dilemma with your function.

    if the start date is a saturday and the end date is a sunday it returns a -1.

    i think this should just return 0.

    no?

    Heh... would you mind posting the exact code you tested with, please?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • i just cut and paste from your code. (dont worry i left your name on there :D)

    so yeah if you put in any consecutive saturday and sunday you get a -1. try the 13th and 14 of this month!

  • That's what I really meant... what were the inputs. Thanks for the 13th/14th of this month. I'll give it a try.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • dulanjan (9/15/2008)


    i just cut and paste from your code. (dont worry i left your name on there :D)

    so yeah if you put in any consecutive saturday and sunday you get a -1. try the 13th and 14 of this month!

    Not sure what you're doing differently, but I get 0 from both the "raw" code and the function...

    Here's what I ran...

    DECLARE @StartDate DATETIME,

    @EndDate DATETIME

    SELECT @StartDate = '20080913',

    @EndDate = '20080914'

    SELECT

    (DATEDIFF(dd, @StartDate, @EndDate) + 1)

    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS RawCode

    SELECT dbo.fn_WorkDays(@StartDate,@EndDate) AS FunctionCode

    ... and here are the results...

    RawCode

    -----------

    0

    (1 row(s) affected)

    FunctionCode

    ------------

    0

    (1 row(s) affected)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff/Forum,

    I love the simplicity of the function, and the fact that it does return the number of business days between two dates. However, what if I needed to create a trigger to update a FOLLOWUP (DATETIME column) automatically with a date that is either 3 or 15 business days out (not worried about holidays). So I try the following:

    DECLARE @BizDays15 TINYINT

    ,@StartDate DATETIME

    ,@EndDate DATETIME

    SET @StartDate = '10/1/2008'

    Set @EndDAte = @StartDate + 15

    SET @BizDays15 = (SELECT

    (DATEDIFF(dd, @StartDate, @EndDate) + 1)

    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)

    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END))

    select @BizDays15, @StartDate, @endDAte + 3

    select case

    when @BizDays15 < 15

    then (select @EndDate + (15-@BizDays15))

    else @EndDate

    end

    My result when I do this is the 19th of Oct. which is a Sunday L. Of course I need it to be the 22nd of Oct. Any thoughts on this?

    Thanks,

    Fraggle

Viewing 15 posts - 91 through 105 (of 156 total)

You must be logged in to reply to this topic. Login to reply