skull52
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?
maybe a simple solution
create a table as
Calendar(calDate, wd, isWeekend, isHoliday, Description)
as
then update it with some holidays by setting isHoliday=1 and add some description to those dates
then just a simple select
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
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
you may want a small function if you want
then use
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;
then use
select getNextBusinessDay(getdate(), 24) as BDafter24
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
hope it works...
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;
hope it works...
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.
Not sure how to modify your solution to what I need, I just need to have a the NEXTCNTDT be 181 days from LASTCNTDT.
ASKER
Huseyin,
what are the datatypes for the calendar table?
what are the datatypes for the calendar table?
Calendar(calDate date, wd tinyint, isWeekend bit, isHoliday bit, description nvarchar(100))
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
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
bit (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql
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?
I did not create any object/data here...
so, is it working now?
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
select min(calDate) NextDay from calendar c
where c.CalDate>CONVERT(DATETIME
the qry is wrong, create the function I provided and use that one (the one with row_number) @ ID: 42129387
ASKER
OK, what would be the syntax of the script to to get the results i want
select dbo.getNextBusinessDay(cas t(CONVERT( DATETIME, '2017-05-09 00:00:00', 102) as date), 181) as BDafter24
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
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
ASKER
I cant create the function because of the error
here
then use
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;
then use
select dbo.getNextBusinessDay(CONVERT(DATE, '2017-05-09', 102), 181) as NextDay
ASKER
Bingo....
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...
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...
ASKER
2 years of holidays should be enough
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EE should give me an award for this work ha ha...
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.
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.
ASKER
I agree
Knock yourself out..