We help IT Professionals succeed at work.

Fix Code to Accept Negative Input for AddBusinessDays to date?

129 Views
Last Modified: 2018-11-30
Hi Experts,

Can anyone tweak this code, below, to work with a negative date input to subtract business days from a given date?

Thanks,
Steve

ALTER function [dbo].[DateAddWorkDay]
(@days int,@FromDate Date)
returns Date
as
begin
declare @result date
set @result = (
select b
from
(
    SELECT
    b,
       (DATEDIFF(dd, a, b))
      -(DATEDIFF(wk, a, b) * 2)
      -(CASE WHEN DATENAME(dw, a) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, b) = 'Saturday' THEN 1 ELSE 0 END)
      -COUNT(o.Holiday) 
      as workday
    from
    (
    select 
    @FromDate as a,
    dateadd(DAY,num +@days,@FromDate) as b
    from (select row_number() over (order by (select NULL)) as num
          from Information_Schema.columns
         ) t
    where num <= 100 
    ) dt
    left join Holiday o on o.Holiday between a and b and DATENAME(dw, o.Holiday) not in('Saturday','Sunday') 
    where DATENAME(dw, b) not in('Saturday','Sunday')
          and b not in (select Holiday from Holiday where Holiday between a and b) 

    group by a,b
) du
where workday =@days 


)
return @result 
end

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
what's the issue for your function posted?

you already have this:

dateadd(DAY,num +@days,@FromDate) as b

Open in new window


it should worked if @days is negative too
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
For negative @Days input, if I interpret your function correctly, you might need to change the code in 2 places:
1. Go back to the dates instead of checking forward
dateadd(DAY,-num +@days,@FromDate)

Open in new window

to
dateadd(DAY,case when num > 0 then num +@days else -num+@days end,@FromDate)

Open in new window


2. and the below WHERE condition in 2 places
where condition from
o.Holiday between a and b 

Open in new window

to
((@days > 0 AND o.Holiday BETWEEN a and b) OR (@days < 0 and o.Holiday between b and a))

Open in new window

to get the records fetched out..
If possible, kindly provide the table structure of holidays table so that I can check it out once whether this will work fine or not..

Author

Commented:
Thanks guys for the input. I tried your suggestion, Raja, and I'm getting a null output in both + and - days cases.

Here's the Holiday table, below, with the code with your suggested edits.

ALTER function [dbo].[DateAddWorkDay]
(@days int,@FromDate Date)
returns Date
as
begin
declare @result date
set @result = (
select b
from
(
    SELECT
    b,
       (DATEDIFF(dd, a, b))
      -(DATEDIFF(wk, a, b) * 2)
      -(CASE WHEN DATENAME(dw, a) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, b) = 'Saturday' THEN 1 ELSE 0 END)
      -COUNT(o.Holiday) 
      as workday
    from
    (
    select 
    @FromDate as a,
       dateadd(DAY,case when num > 0 then num +@days else -num+@days end,@FromDate) as b
    from (select row_number() over (order by (select NULL)) as num
          from Information_Schema.columns
         ) t
    where num <= 100 
    ) dt
        left join Holiday o on 
		((@days > 0 AND o.Holiday BETWEEN a and b) OR (@days < 0 and o.Holiday between b and a)) 
		AND DATENAME(dw, o.Holiday) not in('Saturday','Sunday') 
    where DATENAME(dw, b) not in('Saturday','Sunday')
          and b not in (select Holiday from Holiday where Holiday between a and b) 

    group by a,b
) du
where workday =@days 


)
return @result 
end

Open in new window



CREATE TABLE [dbo].[Holiday](
	[Holiday] [date] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-01-01' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-01-01' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-09-03' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-11-22' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-11-23' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-12-25' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-12-26' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-12-27' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-12-28' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2018-12-31' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-01-01' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-05-27' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-07-04' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-09-02' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-11-28' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-11-29' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-12-25' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-12-26' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-12-27' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-12-30' AS Date))
GO
INSERT [dbo].[Holiday] ([Holiday]) VALUES (CAST(N'2019-12-31' AS Date))
GO

Open in new window

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks very much,gentlemen, in particular Raja!
All the Best,
Steve
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome, Steve!!