JeeTee (11/21/2014)

---

As I'm thinking about this more (with the somewhat limited amount of information I have) two things come to mind.

The first is to build a calendar table; basically an enhanced tally table with the breakdowns of dates, years, months, weekends, etc. This gets you over the hurdle of a lot of the date masking and weekend problems. I've provided a sample script of this below.

But the second thing is, why do you have to do this all on the fly each time? Since the holidays and weekends aren't going to change (presumably unless you update the contents of your bcn_holidays and bcn_holidays_compute tables), you can just run this once and persist off as many years into the future you like. Even if the initial population takes half an hour to go 5000 years into the future or something, after the initial run, all your queries could then just reference a new, precalculated calendar table with holidays already excluded.

Calendar Table Code

declare @eMsg varchar(2000)

if object_id('tempdb.dbo.#cal') is not null drop table #cal

create table #cal

(

cDateInt int primary key clustered,

cDate as cast(cast(cDateInt as datetime) -2 as date) persisted,

cDateDt as cast(cDateInt as datetime) -2 persisted,

cMonthChar as cast(case when datepart(month, cast(cDateInt as datetime) -2) < 10 then '0'

else ''

end + + cast(datepart(month, cast(cDateInt as datetime) - 2) as varchar(30)) as char(2)) persisted,

cMonthInt as cast(datepart(month, cast(cDateInt as datetime) - 2) as tinyint) persisted,

cQtrChar as cast(datepart(qq, cast(cDateInt as datetime) - 2) as char(1)) persisted,

cQtrInt as cast(datepart(qq, cast(cDateInt as datetime) - 2) as tinyint) persisted,

cYearChar as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as char(4)) persisted,

cYearInt as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as smallint) persisted,

cWeekdayNum as datepart(weekday, cast(cDateInt as datetime) - 2),

cWeekdayName as datename(weekday, cast(cDateInt as datetime) -2) ,

cMonthName as datename(month, cast(cDateInt as datetime) - 2),

isWeekend as case datepart(weekday, cast(cDateInt as datetime) - 2) when 1 then 1

when 7 then 1

else 0

end

)

insert into #cal (cDateInt)

select top 100000 row_number() over (order by (select null))- 1

from sys.objects a, sys.objects b