troubleshooting Question

Fix Code to Accept Negative Input for AddBusinessDays to date?

Avatar of tablaFreak
tablaFreakFlag for United States of America asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL
6 Comments1 Solution140 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros