SQL populating calendar dates for 2 week timetable

Question

I have two tables one for the timetable days:

+--------+---------+
| intDay | DayName |
+--------+---------+
|      1 | Mon A   |
|      2 | Tue A   |
|      3 | Wed A   |
|      4 | Thu A   |
|      5 | Fri A   |
|      6 | Mon B   |
|      7 | Tue B   |
|      8 | Wed B   |
|      9 | Thu B   |
|     10 | Fri     |
+--------+---------+

And a second table with term dates:

+-----------+------------+------------+
| Term Name | Term Start |  Term End  |
+-----------+------------+------------+
| Term A    | 07/09/2020 | 15/12/2020 |
| Term B    | 10/01/2021 | 15/03/2021 |
| Term C    | 01/04/2021 | 03/07/2021 |
+-----------+------------+------------+

There is a third table that I can potentially use which contains the calendered week number as well as whether it's week A or B.

The structure for this is:

+-------------+---------+---------+
| intYearWeek | intYear | intWeek |
+-------------+---------+---------+
|          37 |    2020 |       1 |
|          38 |    2020 |       2 |
|          39 |    2020 |       1 |
|          40 |    2020 |       2 |
|          41 |    2020 |       1 |
|          42 |    2020 |       2 |
+-------------+---------+---------+

intYearWeek is the calendared week number of the year, intYear is the academic year (Sep-Jul) and Week A and B is represented as 1 or 2 respectively. These are only teaching weeks so excludes any holiday weeks.

What I'd like to do is generate a list of calendared dates for the whole academic year using the above the two-week cycle but only during term time. I would match these back to the Staff and Pupils timetables to generate an Outlook import.

So if we take Week numbers 37-40 (7th September 2020 - 28th September 2020), the result would look something like this:

+-----------------+------------+
| Week_Number Day |    Date    |
+-----------------+------------+
| 37 Mon A        | 07/09/2020 |
| 37 Tue A        | 08/09/2020 |
| 37 Wed A        | 09/09/2020 |
| 37 Thu A        | 10/09/2020 |
| 37 Fri A        | 11/09/2020 |
| 38 Mon B        | 14/09/2020 |
| 38 Tue B        | 15/09/2020 |
| 38 Wed B        | 16/09/2020 |
| 38 Thu B        | 17/09/2020 |
| 38 Fri B        | 18/09/2020 |
| 39 Mon A        | 21/09/2020 |
| 39 Tue A        | 22/09/2020 |
| 39 Wed A        | 23/09/2020 |
| 39 Thu A        | 24/09/2020 |
| 39 Fri A        | 25/09/2020 |
| 40 Mon B        | 28/09/2020 |
| 40 Tue B        | 29/09/2020 |
| 40 Wed B        | 30/09/2020 |
| 40 Thu B        | 01/10/2020 |
| 40 Fri B        | 02/10/2020 |
+-----------------+------------+

Thanks in advance for your help!

user_1607951 · Answer

Approach:

Create all dates between the start of the school year and the end of the school year with a recursive common table expression (cte_TermDays).
Filter out the weekend days and days outside the school terms (where clause).
Combine the day of the week number with the week type to determine week A or B and fetch the day name.

Sample data

create table TermPeriods
(
  Name nvarchar(6),
  StartDate date,
  EndDate date
);

insert into TermPeriods (Name, StartDate, EndDate) values
('Term A', '2020-09-07', '2020-12-15'),
('Term B', '2021-01-10', '2021-03-15'),
('Term C', '2021-04-01', '2021-07-03');


create table DayNames
(
  dayNum int,
  dayName nvarchar(5)
);

insert into DayNames (dayNum, dayName) values
( 1, 'Mon A'),
( 2, 'Tue A'),
( 3, 'Wed A'),
( 4, 'Thu A'),
( 5, 'Fri A'),
( 6, 'Mon B'),
( 7, 'Tue B'),
( 8, 'Wed B'),
( 9, 'Thu B'),
(10, 'Fri B');

create table WeekTypes
(
  YearNum int,
  WeekNum int,
  WeekType int
);

insert into WeekTypes (YearNum, WeekNum, WeekType) values
(2020, 37, 1),
(2020, 38, 2),
(2020, 39, 1),
(2020, 40, 2),
(2020, 41, 1),
(2020, 42, 2);

Solution

with cte_TermDays as
(
  select min(StartDate) as [TermDate], max(EndDate) as [Stop]
  from TermPeriods
    union all
  select dateadd(day, 1, td.TermDate), td.Stop
  from cte_TermDays td
  where td.TermDate < td.Stop
)
select td.TermDate as [Date],
       datepart(DW, td.TermDate)-1 as [DayPart],
       datepart(WW, td.TermDate) as [WeekPart],
       datepart(YY, td.TermDate) as [YearPart],
       wt.WeekType,
       (datepart(DW, td.TermDate)-1)+(wt.WeekType-1)*5 as [DayNum],
       dn.DayName,
       convert(nvarchar(2), wt.WeekNum) + ' ' + dn.DayName as [Week_Number Day]
from cte_TermDays td
join WeekTypes wt
  on  wt.YearNum = datepart(YY, td.TermDate)
  and wt.WeekNum = datepart(WW, td.TermDate)
join DayNames dn
  on dn.DayNum = (datepart(DW, td.TermDate)-1)+(wt.WeekType-1)*5
where datepart(DW, td.TermDate) > 1 --exclude sunday
  and datepart(DW, td.TermDate) < 7 --exclude saturday
  and exists (  select top 1 'x'    --limit to term periods
                from TermPeriods tp
                where tp.StartDate <= td.TermDate
                  and tp.EndDate >= td.TermDate )
order by td.TermDate
option (maxrecursion 366);

Result

Date       DayPart     WeekPart    YearPart    WeekType    DayNum      DayName Week_Number Day
---------- ----------- ----------- ----------- ----------- ----------- ------- ---------------
2020-09-07 1           37          2020        1           1           Mon A   37 Mon A
2020-09-08 2           37          2020        1           2           Tue A   37 Tue A
2020-09-09 3           37          2020        1           3           Wed A   37 Wed A
2020-09-10 4           37          2020        1           4           Thu A   37 Thu A
2020-09-11 5           37          2020        1           5           Fri A   37 Fri A
2020-09-14 1           38          2020        2           6           Mon B   38 Mon B
2020-09-15 2           38          2020        2           7           Tue B   38 Tue B
2020-09-16 3           38          2020        2           8           Wed B   38 Wed B
2020-09-17 4           38          2020        2           9           Thu B   38 Thu B
2020-09-18 5           38          2020        2           10          Fri B   38 Fri B
2020-09-21 1           39          2020        1           1           Mon A   39 Mon A
2020-09-22 2           39          2020        1           2           Tue A   39 Tue A
2020-09-23 3           39          2020        1           3           Wed A   39 Wed A
2020-09-24 4           39          2020        1           4           Thu A   39 Thu A
2020-09-25 5           39          2020        1           5           Fri A   39 Fri A
2020-09-28 1           40          2020        2           6           Mon B   40 Mon B
2020-09-29 2           40          2020        2           7           Tue B   40 Tue B
2020-09-30 3           40          2020        2           8           Wed B   40 Wed B
2020-10-01 4           40          2020        2           9           Thu B   40 Thu B
2020-10-02 5           40          2020        2           10          Fri B   40 Fri B
2020-10-05 1           41          2020        1           1           Mon A   41 Mon A
2020-10-06 2           41          2020        1           2           Tue A   41 Tue A
2020-10-07 3           41          2020        1           3           Wed A   41 Wed A
2020-10-08 4           41          2020        1           4           Thu A   41 Thu A
2020-10-09 5           41          2020        1           5           Fri A   41 Fri A
2020-10-12 1           42          2020        2           6           Mon B   42 Mon B
2020-10-13 2           42          2020        2           7           Tue B   42 Tue B
2020-10-14 3           42          2020        2           8           Wed B   42 Wed B
2020-10-15 4           42          2020        2           9           Thu B   42 Thu B
2020-10-16 5           42          2020        2           10          Fri B   42 Fri B

SQL populating calendar dates for 2 week timetable

1 Answers