There are loads of good scripts out there for creating calendar tables so I’ve never felt the need to put up my own. But since I’ve written an article that references the need for one I’ll chuck my own into the mix as well. This will be a timetabling academic year calendar. So rather than just dates it will go down to the smallest timetableable unit – 15 minutes – so each row will be a 15 minute slot. For the whole year there will be about 35,000 rows (365 * 96). The final table will look like this.
create table calendar_timetable ( academic_year int , semester varchar(20) , week_number int , day_number int , day_name varchar(10) , time_slot_number int , time_slot_start datetime primary key clustered , day_period_type varchar(50) , time_period_type varchar(50) )
The day_period_type and time_period_type will allow periods of days and periods of time respectively to be categorised. In order to define these periods I’ll define a new table type to hold them.
CREATE TYPE time_period AS TABLE ( time_period_name varchar(50) , time_period_start datetime , time_period_end datetime )
Here are some examples for the day_period_type and time_period_type:
declare @time_periods time_period
insert into @time_periods values ('Night', '00:00:00', '07:45:00')
insert into @time_periods values ('Morning', '08:00:00', '11:45:00')
insert into @time_periods values ('Afternoon', '12:00:00', '18:00:00')
insert into @time_periods values ('Evening', '18:15:00', '21:00:00')
insert into @time_periods values ('Night', '21:15:00', '23:45:00')
declare @time_period_types time_period
insert into @time_period_types values ('Before Start', '24-Aug-2020', '13-Sep-2020')
insert into @time_period_types values ('Induction', '14-Sep-2020', '20-Sep-2020')
insert into @time_period_types values ('Progress Week', '02-Nov-2020', '08-Nov-2020')
insert into @time_period_types values ('Christmas', '21-Dec-2020', '03-Jan-2021')
insert into @time_period_types values ('Assessments', '04-Jan-2021', '17-Jan-2021')
insert into @time_period_types values ('Progress Week', '01-Mar-2021', '07-Mar-2021')
insert into @time_period_types values ('Easter', '29-Mar-2021', '11-Apr-2021')
insert into @time_period_types values ('Assessments', '03-May-2021', '16-May-2021')
insert into @time_period_types values ('Reassessments', '05-Jul-2021', '11-Jul-2021')
insert into @time_period_types values ('After End', '12-Jul-2021', '22-Aug-2021')
I’ll also use this type to define the semester start and end dates and set up two variables to hold the academic year start and end dates.
declare @academic_year_start datetime = '24-AUG-2020'
declare @academic_year_end datetime = '22-AUG-2021'
declare @semesters time_period
insert into @semesters values ('Semester 1', '24-AUG-2020', '17-JAN-2021')
insert into @semesters values ('Semester 2', '18-JAN-2021', '06-JUN-2021')
That’s the structure and data defined. Then just the script to generate the calendar. The innermost query generates a date for each day in the defined year using an integers table.
select dateadd(day, i.number - 1, @academic_year_start) calendar_date , i.number day_number from integers i where i.number <= DATEDIFF(day,@academic_year_start,@academic_year_end) + 1
This is then used as the input for the following query that adds in the week number (this assumes the year starts on a Monday!), day, semester and categorisation of periods of days.
select calendar_date , ((day_number - 1) / 7) + 1 week_number , day_number , datename(WEEKDAY, GETDATE()) day_of_week , s.time_period_name semester , isnull(pt.time_period_name, 'Teaching') time_period_type from ( subquery ) sub left join @semesters s on sub.calendar_date between s.time_period_start and s.time_period_end left join @time_period_types pt on sub.calendar_date between pt.time_period_start and pt.time_period_end
Again this is input to the next query which cross joins to integers to get the 96 fifteen minute records per day.
select * , dateadd(minute, (i2.number - 1) * 15, calendar_date) time_slot from ( subquery ) sub2 join integers i2 on i2.number <= 96
And finally add in the time period categories as defined and produce the final output:
select year(@academic_year_start) academic_year , sub3.semester , sub3.week_number , sub3.day_number , datename(WEEKDAY, sub3.time_slot) day_name , sub3.number time_slot_number , sub3.time_slot time_slot_start , sub3.time_period_type day_period_type , tp.time_period_name time_period_type from ( subquery ) sub3 join @time_periods tp on cast(cast(sub3.time_slot as time) as datetime) between tp.time_period_start and tp.time_period_end
Of course I could define two separate tables to implement a traditional calendar and the timeslot element separately. This would save a few rows but I much prefer a single table with and a single join from the fact table. Below is the full script.
select year(@academic_year_start) academic_year , sub3.semester , sub3.week_number , sub3.day_number , datename(WEEKDAY, sub3.time_slot) day_name , sub3.number time_slot_number , sub3.time_slot time_slot_start , sub3.time_period_type day_period_type , tp.time_period_name time_period_type from ( select * , dateadd(minute, (i2.number - 1) * 15, calendar_date) time_slot from ( select calendar_date , ((day_number - 1) / 7) + 1 week_number , day_number , datename(WEEKDAY, GETDATE()) day_of_week , s.time_period_name semester , isnull(pt.time_period_name, 'Teaching') time_period_type from ( select dateadd(day, i.number - 1, @academic_year_start) calendar_date , i.number day_number from integers i where i.number <= DATEDIFF(day,@academic_year_start,@academic_year_end) + 1 ) sub left join @semesters s on sub.calendar_date between s.time_period_start and s.time_period_end left join @time_period_types pt on sub.calendar_date between pt.time_period_start and pt.time_period_end ) sub2 join integers i2 on i2.number <= 96 ) sub3 join @time_periods tp on cast(cast(sub3.time_slot as time) as datetime) between tp.time_period_start and tp.time_period_end