CODEWITHSUNDEEP
t-sql
Zack N-D
Zack N-D

Reputation: 21

Have Datetable with dates and if business day, need to find the 11th business day after a date

I need to find a date that is 11 business days after a date.

I did not have a date table. Requested one, long lead time for one. Used a CTE to produce results that have a datekey, 1 if weekday, and 1 if holiday, else 0. Put those results into a Table Variable, now Business_Day is (weekday-holiday). Much Googling has already happened.

select dt.Datekey,
(dt.Weekdaycount - dt.HolidayCount) as Business_day
from @DateTable dt[enter image description here][1]

UPDATE, I've figured it out in Excel. Running count of business days, a column of business day count + 11, then a Vlookup finding the +11 date . Now how do I do that in SQL?

Results like this Datekey 2019-01-01 Business_day 0 Datekey 2019-01-02 Business_day 1

Upvotes: 2

Views: 69

Answers (2)

Zack N-D
Zack N-D

Reputation: 21

1st step was to create a date table. Figuring out weekday verse weekends is easy. Weekdays are 1, weekends are 0. Borrowed someone else's holiday calendar, if holiday 1 else 0. Then Business day is Weekday-Holiday = Business Day. Next was to create a running total of business days. That allows you to move from whatever running total day you're current on to where you want to be in the future, say plus 10 business days. Hard coded key milestones in the date table for 2 and 10 business days. Then JOIN your date table with your transaction table on your zero day and date key. Finally this allows you to make solid calculations of business days.

WHERE CONVERT(date,  D.DTRESOLVED) <= CONVERT(date, [10th_Bus_Day])

Upvotes: 0

Ali Al-Mosawi
Ali Al-Mosawi

Reputation: 778

I will assume you want to set your weekdays, and you can enter the holidays in a variable table, so you can do the below:-

here set the weekend names

Declare @WeekDayName1 varchar(50)='Saturday'
Declare @WeekDayName2 varchar(50)='Sunday'

Set the holiday table variable, you may have it as a specific table your database

Declare @Holidays table (
[Date] date,
HolidayName varchar(250)
)

Lets insert a a day or two to test it.

insert into @Holidays values (cast('2019-01-01' as date),'New Year')
insert into @Holidays values (cast('2019-01-08' as date),'some other holiday in your country')

lets say your date you want to start from is action date and you need 11 business days after it

Declare @ActionDate date='2018-12-28'
declare @BusinessDays int=11

A recursive CTE to count the days till you get the correct one.

;with cte([date],BusinessDay) as (
    select @ActionDate [date],cast(0 as int) BusinessDay
    union all
    select dateadd(day,1,cte.[date]),
    case 
        when DATENAME(WEEKDAY,dateadd(day,1,cte.[date]))=@WeekDayName1
             OR DATENAME(WEEKDAY,dateadd(day,1,cte.[date]))=@WeekDayName2
             OR (select 1 from @Holidays h where h.Date=dateadd(day,1,cte.[date])) is not null 
                then cte.BusinessDay
                else cte.BusinessDay+1 
                end BusinessDay
     From cte where BusinessDay<@BusinessDays
    )
--to see the all the dates till business day + 11 
--select * from cte option (maxrecursion 0)
--to get the required date
select MAX([date])  from cte option (maxrecursion 0)

In my example the date I get is as below:-

ActionDate =2018-12-28
After 11 business days :2019-01-16

Hope this helps

Upvotes: 1

Related Questions