t-sql Function Working Days

RRR Harischandra
RRR Harischandra used Ask the Experts™
on
Hi Experts,

I have written the following code  to get Working Days.
For example, you will enter Date  then You will enter total number of days. it will return the working date.
if you enter 2019-05-11 and enter 27 days
The function should be return 2019-04-04
But this function is returning
2019-04-09


See the code below

ALTER FUNCTION sr_WeekDaysFc(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
    WHILE @numDays>=0
    BEGIN
       SET @addDate=DATEADD(d,-1,@addDate)
       IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,-1,@addDate)
       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,-1,@addDate)
  
       SET @numDays=@numDays-1
    END
  
    RETURN CAST(@addDate AS DATETIME)
END
GO

Open in new window

Any idea appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Checketh thee out, then download the code, edit the weekends and holidays to fit your definition of 'Working Days', then you'll have a table that you can JOIN to without having to re-query from scratch every time.   SQL Server Calendar Table

I see this is your first question.  Welcome to Experts Exchange.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION sr_WeekDaysFc(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
RETURN (
    SELECT DATEADD(DAY, -(wholeWeeks * 7 + CASE WHEN
        DATEDIFF(DAY, 0, @addDate) + remainingDays >= 5 THEN 2 END), @addDate) AS returnDate
    FROM (
        SELECT @numDays / 5 AS wholeWeeks, @numDays % 5 AS remainingDays
    ) AS calcs1
)
END
GO

Author

Commented:
@Scott Pletcher
Thanks But it'd not working
SELECT dbo.sr_WeekDaysFc('2019-05-07',27)
Results : 2019-03-31
But Expected Results is :2019-03-29 (Becuase 31 and 30 is sat & sunday)
you can check with Excel Workdays('2019-05-07',-27)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Oops, good point, I originally wrote it for going forward with business days, which is more typical, rather than backward.  I'll need to adjust the other code for that too.

It would of have been very helpful if you'd included more sample dates and results in the original post :-).  [ I literally don't have time to work up sample data for every q I answer. ]
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION sr_WeekDaysFc(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
RETURN (
    SELECT DATEADD(DAY, -(wholeWeeks * 7 + remainingDays +
        CASE WHEN DATEDIFF(DAY, 0, @addDate) % 7 - remainingDays < 0
        THEN 2 ELSE 0 END), @addDate) AS returnDate
    FROM (
        SELECT @numDays / 5 AS wholeWeeks, @numDays % 5 AS remainingDays
    ) AS calcs1
)
END
GO
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
How's it going there Hoss?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial