When you are building or creating rudimentary BI for someone, there are always some parameters you have to embed in your query. But, almost always there is date to date parameters. So, it is very helpful to have a calendar table, where you know which days are weekends, which days are working days, what week of the year it is, to which quarter does some date belong to, etc…
So first create table:
CREATE TABLE dbo.Calendar
( date SMALLDATETIME NOT NULL PRIMARY KEY CLUSTERED,
weekend bit,
year bit,
quartal bit,
month bit,
day bit,
day_of_week bit,
month_name varchar(20),
day_name varchar(20),
week_in_year bit
)
Now we have to fill the table. For this query, you must set your start date, maximal date, language for names and type of query to execute.
My start date is 01.01.2010, end date is 31.12.2050, language Croatian and I’ll work with recursive CTE query.
SET LANGUAGE CROATIAN – can use also SET LANGUAGE HRVATSKI
declare @begdate datetime,
@enddate datetime
set @begdate = ‘2010-01-01’
set @enddate = ‘2050-12-31’
;
with Ctecalendar(date,isweekend, y, q,m,d,dw,monthname,dayname,w) as –define CTE
(
select @begdate ,
case when datepart(dw,@begdate ) in (6,7) then 1 else 0 end, –saturday and sunday
year(@begdate ),
datepart(qq,@begdate ),
datepart(mm,@begdate ),
datepart(dd,@begdate ),
datepart(dw,@begdate ),
datename(month, @begdate ),
datename(dw, @begdate ),
datepart(wk, @begdate )
union all
select date + 1,
case when datepart(dw,date + 1) in (6,7) then 1 else 0 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from Ctecalendar where date + 1<= @enddate
)
insert into dbo.Calendar
select date,isweekend, y, q,m,d,dw,monthname,dayname,w from Ctecalendar
option(maxrecursion 32767) –bad query can cause infinitive loop, so you set this to loop 32767 times, 0 for infinitive
So there you have it, simple Calendar table for your queries!
