SQLTeam.com | Weblogs | Forums

Count the number of working days from a date

Hi, I'm hoping you guys can help me as I'm scratching my head on what to do next.

I have a piece of code that will extract accounts where there have been 12 days since the date of the last attempt, however, this includes every day but I only want to include working days (Mon - Fri). How can i only count the number of working days from the date of the last attempt?

select CustomerID
from gbr_creditsolve
Where campaign = 'GBR AA' and  Convert(varchar, Date_of_Last_Attempt2+12,23) = convert(varchar,getdate(),23) 

Example Value in table

2020-07-01 00:00:00.000

Thanks in advance

select datediff(DW,date1,date2) from data

.... datediff .. DW = weekday

Thanks for your help, it's much appreciated. I think I'm getting closer but I'm not there yet.
Using dw date diff is still returning me the total days since the Date_of_Last_Attempt2 and not the working days.

I would expect to see results with a Date_of_Last_Attempt2 = 2020-07-20 (12 Working days)

select customerid, Date_of_Last_Attempt2, getdate()
from gbr_creditsolve
Where campaign = 'GBR AA' and datediff(DW,Date_of_Last_Attempt2,getdate()) = 12 and datediff(day,importdate,getdate()) = 0

Results

12345678 2020-07-24 00:00:00.000 2020-08-05 15:50:13.370
876543321 2020-07-24 00:00:00.000 2020-08-05 15:50:13.370

hi

are you on SQL Server
.. if so which version !!!

Please do let me know !!

if its SQL Server I will try to find a solution for you !!!

A usual approach would be to create a calendar table and then join to it. eg:

https://www.sqlshack.com/designing-a-calendar-table/

Yes I'm on SQL server 2012

Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 10.0.16299.15
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.16299.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.16299

there is a solution ..

there is function written by Jeff Moden ( special thanks to Jeff author of so many useful articles )
it takes care of a lot of things .. please see the comments in his function

please click arrow to the left for Jeff's Function
CREATE 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. MASTER.dbo.fn_WorkDays(@StartDate,@EndDate)
 2. MASTER.dbo.fn_WorkDays(@StartDate,DEFAULT)    --Always returns 1 or 0
 3. MASTER.dbo.fn_WorkDays(@EndDate,@StartDate)
 4. MASTER.dbo.fn_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
 5. MASTER.dbo.fn_WorkDays(@EndDate,@EndDate)     --Always returns 1 or 0
 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)
        (
         @StartDate DATETIME,
         @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
        )
--===== 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
        --===== 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)
        --===== 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)
                )
    END
GO

please use it like this
i have not TESTED

select customerid, Date_of_Last_Attempt2, getdate()
from gbr_creditsolve
Where campaign = 'GBR AA' and  dbo.fn_WorkDays(Date_of_Last_Attempt2,getdate())  = 12 and datediff(day,importdate,getdate()) = 0

Here is my testing from my own test data
select * from data a cross apply (select dbo.fn_WorkDays(a.date1,a.date2) as weekdays) as x

image

image

I strongly urge that you create two separate tables, workdays and nonworkdays. That will simplify queries and improve overall performance. NOTE: you MUST completely load the nonworkdays table first, then the workdays table.

Here's some example code for 2020, adjust as needed for your specific work calendar:

CREATE TABLE dbo.nonwork_dates (
    nonwork_date date NOT NULL,
    CONSTRAINT nonwork_dates__PK PRIMARY KEY CLUSTERED ( nonwork_date ) WITH ( FILLFACTOR = 100 ),
    nonwork_reason_code tinyint NULL /*1=New Year's Day; 2=MLK; 3=...;*/
    )
INSERT INTO dbo.nonwork_dates ( nonwork_date ) VALUES
    ('2020-01-01'),('2020-05-25'),('2020-07-03'),('2020-09-07'),
    ('2020-11-26'),('2020-11-27'),('2020-12-25')

CREATE TABLE dbo.work_dates (
    work_date date NOT NULL,
    CONSTRAINT work_dates__PK PRIMARY KEY CLUSTERED ( work_date ) WITH ( FILLFACTOR = 100 )
    )

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number    
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
INSERT INTO dbo.work_dates ( work_date )
SELECT DATEADD(DAY, t.number - 1, '20200101') AS work_date
FROM cte_tally1000 t
WHERE t.number BETWEEN 1 AND 366 AND
    DATEDIFF(DAY, 0, DATEADD(DAY, t.number - 1, '20200101')) % 7 <= 4 /*exclude Sat and Sun*/ AND
    NOT EXISTS(SELECT 1 FROM dbo.nonwork_dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number - 1, '20200101'))
ORDER BY 1

With that out of the way, the query becomes something like below. Naturally you could push the +n work dates calc to a function if you wanted to. If you want me to code that function up, just let me know. It will be just like the code you see below, very straightforward and easy to use/follow.

SELECT CustomerID
FROM dbo.gbr_creditsolve
CROSS APPLY (
    SELECT TOP (1) work_date AS work_date_12
    FROM (
        SELECT TOP (12) work_date 
        FROM dbo.work_dates 
        WHERE work_date > GETDATE() 
        ORDER BY work_date
    ) AS next_12_days
    ORDER BY work_date DESC
) as calc_12_work_days_out
WHERE campaign = 'GBR AA' AND
    Date_of_Last_Attempt >= work_date_12 AND
    Date_of_Last_Attempt < DATEADD(DAY, 1, work_date_12)

Thanks, everyone for your help, unfortunately I'm unable to create functions or tables so I will have to ask for those permissions before I can continue. I don't suppose there's a way of doing this without the need to create a table or function?

Not a reasonable one. You can always try creating a table in tempdb; if that doesn't work, you should always be able to create a temp table, #table_name, in tempdb. Every use by default has that permission (unless they've gone out of their way to remove it somehow??!).

I just "love" it when people remove more than half the tools a person might use and then tell them to do something where it would be best to use such tools.

In the meantime, you can "steal" from some of the scripts and use them in a CROSS APPLY as if they were a function.

As a bit of a side bar, does the database maybe already have a date table or at least a holiday table?

Hi Jeff, I'm checking to see if the database already has a date table, i cant see one but you never know it might be there somewhere.

Thanks Scott

thank you!!! I have worked in such a place only once in my life. I exited to a new job real quick. I understand the needs to be cautious but sometimes it just does not make any sense.

We want a 4 course meal, you can only use a wooden spatula. Get with it you Luddite!

1 Like

You do have to be a bit careful. One of the things I've found most true is that while "Change is inevitable... change for the better is not."

One of the things I've been "scolded" with is "To a hammer, everything is a nail." I have to sometimes remind people that "When you're trying to drive a nail, you should probably use a hammer and it needs to be the right size hammer. You don't want to use a framing nailer and compressor to drive a brad into a plaster wall to hang a picture." :slight_smile:

@davidmatthewjames
If it doesn't already have one, that's ok. While date tables are certainly convenient for the people writing code, they frequently end up making code quite a bit slower and more resource intensive due to the lookups. So, if you don't have one, post back and I think I can show you how to work around that using only code.

Hi guys, thanks for all the support here. We've now managed to get access rights to create a function and its working as required,

I would like to add again my thanks for helping me get this issue resolved.

Regards

DJ

Thank you for the feedback.