Post reply

Calculating Work Days

  • March 3, 2015 at 8:08 pm

    #1781525

    Awesome. Thanks for the feedback, JeeTee. It's not often that a decade old thread gets feedback like this.

    --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)

  • January 3, 2017 at 1:07 pm

    #1920476

    Just a small update.

    --===== If the function already exists, drop it

    IF OBJECT_ID(N'[dbo].[ifn_WorkDays]') IS NOT NULL

    DROP FUNCTION [dbo].[ifn_WorkDays]

    GO

    GO

    CREATE FUNCTION dbo.ifn_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.

    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".

    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.

    Rev 03 - 01/03/2017 - Luis Cazares - Change the function into an iTVF. Keep the functionality

    */

    (

    @StartDate datetime,

    @EndDate datetime

    )

    RETURNS TABLE

    AS

    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) AS WorkDays

    FROM (SELECT DATEADD(dd,DATEDIFF(dd,0,MIN(adate)),0) AS StartDate, DATEADD(dd,DATEDIFF(dd,0,MAX(adate)),0) AS EndDate

    FROM (VALUES(@StartDate),(@EndDate))x(adate)

    WHERE @StartDate IS NOT NULL)y

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 3, 2017 at 7:29 pm

    #1920528

    Spot on, Luis! Thanks for the update.

    --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)

  • June 8, 2018 at 10:26 am

    #1993415

    I stumbled across this while looking to create my own and would like to propose a couple changes:  
    First, I have noticed lately moving filters from a WHERE clause to a WHEN clause often results in nice performance gains. Also, this:
    SELECT * FROM dbo.ifn_WorkDays(getdate(),NULL)
    Returns a 1. To fix that I changed WHERE @StartDate IS NOT NULL in the subquery to WHEN DATEDIFF(dd, @startDate, @endDate) IS NOT NULL in my CASE statement.... and some schemabinding. Here's the proposed updated version:
    --===== If the function already exists, drop it
    IF OBJECT_ID(N'dbo.ifn_WorkDays') IS NOT NULL
     DROP FUNCTION dbo.ifn_WorkDays
    GO
    CREATE FUNCTION dbo.ifn_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.

    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".

    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.
    Rev 03 - 01/03/2017 - Luis Cazares - Change the function into an iTVF. Keep the functionality
    Rev 04 - 06/08/2018 - Alan Burstein - 1. Moved NULL parameter filering from subquery "x" to the
                  WHERE clause to remove a filter from the execution plan.
                 2. Updated function to return a NULL if either @startDate
                  OR @enddate are NULL.
                 3. Added SCHEMABINDING
    */
    (
      @startDate datetime,
      @endDate  datetime
    )
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT workDays =
      -- If @startDate or @endDate are NULL then rerturn a NULL
    CASE WHEN DATEDIFF(dd, @startDate, @endDate) IS NOT NULL THEN
      (DATEDIFF(dd, startDate, endDate) + 1) --Start with total days including weekends
      -(DATEDIFF(wk, startDate, endDate) * 2) --Subtact 2 days for each full weekend

      -- Subtract 1 when startDate is Sunday and Substract 1 when endDate is Sunday:
      -(CASE WHEN DATENAME(dw, startDate) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, endDate) = 'Saturday' THEN 1 ELSE 0 END)
    END
    FROM
    ( -- if @endDate is earlier that @startDate then flip them
    SELECT StartDate = DATEADD(dd, DATEDIFF(dd,0,MIN(adate)), 0),
       EndDate = DATEADD(dd, DATEDIFF(dd,0,MAX(adate)), 0)
    FROM (VALUES(@startDate),(@endDate)) x(adate))y;
    GO

    Moving the filter from the WHERE clause to the CASE statement changes the execution plan like so:

    Here's a quick performance test I cooked up (dbo.ifn_WorkDays is the original, dbo.ifn_WorkDaysV2 is the updated version):
    -- 1 Million Row Test Harness
    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..#date') IS NOT NULL DROP TABLE #date
    SELECT TOP (1000000)
    d1 = DATEADD(dd, checksum(newid())%2000-2000, getdate()),
    d2 = DATEADD(dd, checksum(newid())%2000-2000, getdate())
    INTO #date
    FROM sys.all_columns a, sys.all_columns b;
    GO

    -- Start Fresh
    DBCC FREEPROCCACHE  with no_infomsgs;
    DBCC DROPCLEANBUFFERS with no_infomsgs;

    IF OBJECT_ID('tempdb..#stats') IS NOT NULL DROP TABLE #stats;
    CREATE TABLE #stats (v tinyint, tm int);
    GO

    PRINT char(13)+char(10)+'Print dbo.ifn_WorkDays'+char(13)+char(10);
    GO
    DECLARE @st datetime = getdate(), @x int;
    SELECT @x = w.WorkDays
    FROM #date d
    CROSS APPLY dbo.ifn_WorkDays(d.d1, d.d2) w
    OPTION (MAXDOP 1)

    INSERT #stats VALUES (1, DATEDIFF(MS, @st, getdate()));
    GO 5

    PRINT char(13)+char(10)+'Print dbo.ifn_WorkDaysV2'+char(13)+char(10);
    GO
    DECLARE @st datetime = getdate(), @x int;
    SELECT @x = w.WorkDays
    FROM #date d
    CROSS APPLY dbo.ifn_WorkDaysV2(d.d1, d.d2) w
    OPTION (MAXDOP 1)

    INSERT #stats VALUES (2, DATEDIFF(MS, @st, getdate()));
    GO 5

    -- raw stats:
    SELECT
    [version] = CASE v WHEN 1 THEN 'old' ELSE 'new' END,
    [time]  = tm
    FROM #stats;

    -- total average:
    SELECT
    [version] = CASE v WHEN 1 THEN 'old' ELSE 'new' END,
    average = AVG(i.tm)
    FROM #stats i
    GROUP BY i.v;

    Note that I tested this on my laptop and used MAXDOP 1 because I was getting a parallel plan that was not improving the performance any. 

    Results:

    version time
    ------- -----------
    old  1080
    old  1070
    old  1076
    old  1074
    old  1113
    new  940
    new  900
    new  943
    new  950
    new  940

    version average
    ------- -----------
    old  1082
    new  934

    That's ~15% performance improvement.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • June 13, 2018 at 9:21 am

    #1993876

    Couldn't help it as when I read through the article and the whole discussion, I saw some obvious opportunities for improvement.
    😎

    Two pointers, an implicit conversion to nvarchar as the datename function returns nvarchar and datediff to 0 is more than five times more expensive than comparing the input values in a case statement.

    Improved function

    USE TEEST;
    GO
    SET NOCOUNT ON
    GO
    CREATE OR ALTER FUNCTION dbo.ITVFN_CALC_WORKING_DAYS
    ---------------------------------------------------------------------
    -- Calculate the number of working days between two input dates.
    -- This function is a rewrite of functions posted in Jeff Moden's
    -- article, Calculate Working Days.
    ---------------------------------------------------------------------
    (
      @STARTDATE DATETIME
     ,@ENDDATE DATETIME
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
    ---------------------------------------------------------------------
    -- Set the date order, just in case
    ---------------------------------------------------------------------
    WITH DATE_ORDER AS
    (
      SELECT
       CASE
        WHEN @STARTDATE <= @ENDDATE THEN @STARTDATE
        ELSE @ENDDATE
       END AS START_DATE
       ,CASE
        WHEN @ENDDATE >= @STARTDATE THEN @ENDDATE
        ELSE @STARTDATE
       END AS END_DATE
    )
    ,DAY_DIFFERENCE AS
    (
      SELECT
       DATEDIFF(DAY,DO.START_DATE,DO.END_DATE) + 1 AS DD_COUNT
       ,DO.START_DATE
       ,DO.END_DATE
       ,CASE
        DATENAME(WEEKDAY, DO.START_DATE)   
        WHEN N'Sunday' THEN -1
        ELSE    0
       END AS START_WD
       ,CASE
        DATENAME(WEEKDAY, DO.END_DATE)    
        WHEN N'Saturday' THEN -1
        ELSE    0
       END AS END_WD
      FROM DATE_ORDER DO
    )
    SELECT
      DD.DD_COUNT + (( -(DATEDIFF(WEEK,DD.START_DATE,DD.END_DATE)) * 2) + DD.START_WD + DD.END_WD) AS WORK_DAYS
    FROM DAY_DIFFERENCE DD;

    A test harness, configured for 1,000,000 rows

    USE TEEST;
    GO
    SET NOCOUNT ON;
    DECLARE @SAMPLE_SIZE  BIGINT  = 1000000;
    DECLARE @FIRST_DATE  DATETIME  = '20160101';
    DECLARE @SD_RANGE   INT   = 3653;
    DECLARE @ED_RANGE   INT   = 120;
    DECLARE @INT_BUCKET  INT   = 0;
    DECLARE @BIGINT_BUCKET BIGINT  = 0;
    DECLARE @DT_BUCKET  DATETIME  = 0;
    DECLARE @timer TABLE
    (
    T_TS DATETIME2(7)  NOT NULL DEFAULT (SYSDATETIME())
    ,T_TXT VARCHAR(50)  NOT NULL
    );
    --/*
    IF OBJECT_ID(N'dbo.TBL_TEST_WORKING_DAYS') IS NOT NULL DROP TABLE dbo.TBL_TEST_WORKING_DAYS;
    CREATE TABLE dbo.TBL_TEST_WORKING_DAYS
    (
      TWD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_WORKING_DAYS_TWD_ID PRIMARY KEY CLUSTERED
     ,TWD_START_DATE DATETIME NOT NULL
     ,TWD_END_DATE  DATETIME NOT NULL
    );
    ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
    , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
         FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
    ,START_DAYS AS
    (
      SELECT
       DATEADD(DAY,(ABS(CHECKSUM(NEWID())) % @SD_RANGE),@FIRST_DATE) AS START_DATE
      FROM NUMS NM
    )
    INSERT INTO dbo.TBL_TEST_WORKING_DAYS WITH (TABLOCK) (TWD_START_DATE,TWD_END_DATE)
    SELECT
      SD.START_DATE
     ,DATEADD(DAY,(ABS(CHECKSUM(NEWID())) % @ED_RANGE),SD.START_DATE) AS END_DATE
    FROM START_DAYS SD;
    -- */
    INSERT INTO @timer(T_TXT) VALUES('DRY RUN')
    SELECT
      @DT_BUCKET = T.TWD_START_DATE
     ,@DT_BUCKET = T.TWD_END_DATE
    FROM dbo.TBL_TEST_WORKING_DAYS T;
    INSERT INTO @timer(T_TXT) VALUES('DRY RUN')
    INSERT INTO @timer(T_TXT) VALUES('ITVFN_CALC_WORKING_DAYS')
    SELECT
      @INT_BUCKET = CW.WORK_DAYS
    FROM dbo.TBL_TEST_WORKING_DAYS T
    CROSS APPLY dbo.ITVFN_CALC_WORKING_DAYS(T.TWD_START_DATE,T.TWD_END_DATE) CW
    OPTION (MAXDOP 1);
    INSERT INTO @timer(T_TXT) VALUES('ITVFN_CALC_WORKING_DAYS')
    INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays Alan')
    SELECT
      @INT_BUCKET = CW.workDays
    FROM dbo.TBL_TEST_WORKING_DAYS T
    CROSS APPLY dbo.ifn_WorkDays(T.TWD_START_DATE,T.TWD_END_DATE) CW
    OPTION (MAXDOP 1);
    INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays Alan')
    INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays_Old')
    SELECT
      @INT_BUCKET = CW.workDays
    FROM dbo.TBL_TEST_WORKING_DAYS T
    CROSS APPLY dbo.ifn_WorkDays_Old(T.TWD_START_DATE,T.TWD_END_DATE) CW
    OPTION (MAXDOP 1);
    INSERT INTO @timer(T_TXT) VALUES('ifn_WorkDays_Old')
    -------------------------------------------------------------------------------
    -- CALCULATE AND DISPLAY THE RESULTS
    -------------------------------------------------------------------------------
    SELECT
    T.T_TXT
    ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
    FROM @timer T
    GROUP BY T.T_TXT
    ORDER BY DURATION;

    Results

    T_TXT                  DURATION
    DRY RUN                  138479
    ITVFN_CALC_WORKING_DAYS  585864
    ifn_WorkDays Alan       1077415
    ifn_WorkDays_Old        1328022

  • June 13, 2018 at 8:09 pm

    #1993937

    Heh... this is a very old article... I don't believe I even knew what an implicit conversion was back then. 😀

    Anyway, thanks for the science, Eirikur.  Being a bit of a speed phreak myself, I always enjoy seeing performance improvments.

    --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)

  • June 14, 2018 at 1:55 am

    #1993950

    Jeff Moden - Wednesday, June 13, 2018 8:09 PM

    Heh... this is a very old article... I don't believe I even knew what an implicit conversion was back then. 😀

    Anyway, thanks for the science, Eirikur.  Being a bit of a speed phreak myself, I always enjoy seeing performance improvments.

    You know how it is, seeing an opportunity like this, one just can't help it.
    😎

Viewing 7 posts - 151 through 156 (of 156 total)

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