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!
Approach:
cte_TermDays).where clause).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