Return Previous business day

Question

Post reply

Return Previous business day

barry.nielson

SSC Eights!

Points: 912
More actions
June 5, 2019 at 12:18 am

#3650277
Hello All
I am looking to return the previous business day from a function.
```
USE [MYDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[F_PreviousBusinessDay]
(
@DateInput DATE
)
RETURNS DATE
AS
BEGIN
DECLARE @DateOutput DATE = @DateInput
DECLARE @returnDate DATE
--Weekend
BEGIN
SET @DateOutput = DATEADD(DAY, 
CASE when datepart (dw,@DateInput) < 3 then datepart (dw,@DateInput) * -1 + -1 ELSE -1 END,
cast(@DateInput as date))
END
--Holidays
IF EXISTS(SELECT HolidayDate from dbo.AAA_Tbl_Admin_Holidays WHERE HolidayDate = @DateInput)
BEGIN
SET @DateOutput = DATEADD(DAY,-1,@DateInput);
SET @DateOutput = (SELECT dbo.F_PreviousBusinessDay(@DateOutput))
END

RETURN @DateOutput;
END

GO
```
The above code is called:
```
select DBO.F_PreviousBusinessDay('2019-06-02') Return_Date

Or 

Declare @attendancedate date;
set @attendancedate = getdate()
select dbo.F_PreviousBusinessDay(@attendancedate)
```
If I run this, the function seems to return the date I passed to the function. This is not right. The above code should return 04-06-2019. However, if I pass 2019-06-02 to the function it returns 2019-05-31. This would indicate that Saturday is being recognised as a day to omit, but not Sunday and it seems not to find the holiday also. for testing I create 2019-06-03 as a test holiday.
So far I think I've worked out that the date is not being read correctly, i.e. 2019-06-03 00:00:00.000, should be 2019-06-03, but I don't know how to resolve.
Can anybody help correct my code. Otherwise, I am looking to return the previous business day, omitting saturday, Sunday, and public Holidays as recorded by my holiday table.
Thanks
- This topic was modified 5 years, 6 months ago by barry.nielson.
- This topic was modified 5 years, 6 months ago by barry.nielson.

Viewing 3 posts - 1 through 2 (of 2 total)

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

Your function is a scalar function, which has terrible performance.

Below is an iTvf (inline table valued function) that will get the last business day

CREATE FUNCTION [dbo].[F_PreviousBusinessDay]
(
@DateInput DATE
)
/* Usage:

-- Single Value
  SELECT PreviousBusinessDay
  FROM [dbo].[F_PreviousBusinessDay](GETDATE());

--Table of Values
  SELECT PreviousBusinessDay
  FROM YourTable
  CROSS APPLY [dbo].[F_PreviousBusinessDay](YourTable.DateField);
*/
RETURNS TABLE
AS RETURN

  -- First get the last (14) dates.  This assumes that there will be a working day in there.
  -- By changing the value in TOP(14), you can get up to 100 days back.
  WITH    T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
     , DATES(D) AS (SELECT TOP(14) DATEADD(dd, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @DateInput)
                    FROM T T1 CROSS JOIN T T2)
  SELECT PreviousBusinessDay = MAX(d.D)
  FROM DATES as d
  WHERE DATEDIFF(dd, 0, d.D) %7 <= 4 -- Exclude weekends ...  0=Monday, 6=Sunday
  AND NOT EXISTS (SELECT 1 FROM dbo.AAA_Tbl_Admin_Holidays AS h
                  WHERE h.HolidayDate = d.D)
GO

How to post data/code on a forum to get the best help.

barry.nielson SSC Eights! Points: 912 More actions · Answer 2

Thanks for your help and effort to code this.

I've run this and it seems to work beautifully. There's no chance I would have figure out this so thanks a lot.