Link to home
Start Free TrialLog in
Avatar of skull52
skull52Flag for United States of America

asked on

Create a Calendar table

I need to calculate a new date based on the number of days. for example NEXTCNTDT needs to be 181 days from LASTCNTDT and exclude Weekends and Holidays. From what I have been reading it looks like I need to create a Calendar table do this, does anyone have a way to do this with or with out a table?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

What a coincidence.  Here's an article I wrote with T-SQL code ready to download and execute.   All you have to do is 'grip it and rip it', then write yourself a SP/function that accepts a date parameter and returns the 181'st date that is not is_holiday or is_weekend.

Knock yourself out..
maybe a simple solution

create a table as

Calendar(calDate, wd, isWeekend, isHoliday, Description)

as

with n as (select top 100 row_number() over(order by number) as N from master..spt_values),
     d as (select cast(getdate()+(n2.n-1)*100+n1.n as date) calDate from n n1, n n2)
--insert into Calendar(calDate, wd, isWeekend, isHoliday, description)
select d.CalDate, DATEPART(WEEKDAY, calDate),
       case when DATEPART(WEEKDAY,calDate) in (1,7) then 1 else 0 end,
       0, 
       case DATEPART(WEEKDAY, calDate) when 7 then 'WE - Saturday' when 1 then 'WE - Sunday' else null end
  from d

Open in new window


then update it with some holidays by setting isHoliday=1 and add some description to those dates
then just a simple select

select min(calDate) NextDay from calendar c
 where c.CalDate>@LASTCNTDT+181 and isHoliday=0 and isWeekend=0

Open in new window

you may want a small function if you want
create function getNextBusinessDay(@date date, @n int) returns date as
begin
  declare @nd date
  select @nd = min(calDate) from calendar c
   where c.CalDate>@date+n and isHoliday=0 and isWeekend=0
  return isnull(@nd, @date+@n);
end;

Open in new window


then use

select getNextBusinessDay(getdate(), 24) as BDafter24

Open in new window

and remember this table will contain data for only 27 years from now-on :)
oops, there is a logical problem here, I will fix it later... but if you like the solution, I can fix the calculation, ie the function to get the correct day
here is the fixed function

create function getNextBusinessDay(@date date, @n int) returns date as
begin
  declare @nd date = @date+@n

  select @nd = calDate from (
  select calDate, row_number() over(order by calDate) rn from calendar c
   where c.CalDate>@date and isHoliday=0 and isWeekend=0
   ) x where rn=@n;

  return @nd;
end;

Open in new window


hope it works...
Avatar of skull52

ASKER

Jim,
Not sure how to modify your solution to what I need, I just need to have a the NEXTCNTDT  be 181 days from LASTCNTDT.
Avatar of skull52

ASKER

Huseyin,
what are the datatypes for the calendar table?
Calendar(calDate date, wd tinyint, isWeekend bit, isHoliday bit, description nvarchar(100))
Avatar of skull52

ASKER

Huseyin,
You had the "insert into Calendar(calDate, wd, isWeekend, isHoliday, description)"  commented out so i just un-commented it, also the isWeekend, isHoliday displays as TRUE or FALSE and not 1 or 0
TRUE/FALSE is ok, it is actually 0 = false, 1=true in db, no worries...

bit (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql
Avatar of skull52

ASKER

Yeah... I figured that out. Thanks. Did you intentionally comment out the table insert?
yes, to select and see the data...
I did not create any object/data here...

so, is it working now?
Avatar of skull52

ASKER

OK... I have created the table and ran the following script and it is not calculating the proper date it is returning 11/07/2017 but should be 01/17/2018 just excluding weekends

select min(calDate) NextDay from calendar c
 where c.CalDate>CONVERT(DATETIME, '2017-05-09 00:00:00', 102)+181 and isWeekend=0
the qry is wrong, create the function I provided and use that one (the one with row_number) @ ID: 42129387
Avatar of skull52

ASKER

OK, what would be the syntax of the script to to get the results i want
select dbo.getNextBusinessDay(cast(CONVERT(DATETIME, '2017-05-09 00:00:00', 102) as date), 181) as BDafter24
Avatar of skull52

ASKER

I get the following error when trying to create the function

Msg 206, Level 16, State 2, Procedure getNextBusinessDay, Line 3
Operand type clash: date is incompatible with int
better
select dbo.getNextBusinessDay(CONVERT(DATE, '2017-05-09', 102), 181) as NextDay

Open in new window

Avatar of skull52

ASKER

I cant create the function because of the error
here
create function getNextBusinessDay(@date date, @n int) returns date as
begin
  declare @nd date = dateadd(day, @n, @date);

  select @nd = calDate from (
  select calDate, row_number() over(order by calDate) rn from calendar c
   where c.CalDate>@date and isHoliday=0 and isWeekend=0
   ) x where rn=@n;

  return @nd;
end;

Open in new window


then use

select dbo.getNextBusinessDay(CONVERT(DATE, '2017-05-09', 102), 181) as NextDay

Open in new window

Avatar of skull52

ASKER

Bingo....
Avatar of skull52

ASKER

now i just have to add holidays
yup... I get

2018-01-18

without any setup... and remember to populate enough data
or you will just get (date + n) as a result...

I did not add any logic for that... Line 3 in the function...
Avatar of skull52

ASKER

2 years of holidays should be enough
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
EE should give me an award for this work ha ha...
Avatar of skull52

ASKER

Huseyin,
Thank you very much, I am going to keep this open till tomorrow if that is OK with you in case i have anymore questions.
Avatar of skull52

ASKER

I agree