Reddit answer

script.sql

use tempdb;
go
set nocount, xact_abort on;
go

drop table if exists Attendance, ClassDate, Person, AttendanceType;
go

drop table if exists Person;
create table Person (
	PersonNumber int not null identity(1,1),
	FirstName nvarchar(100) not null,
	MiddleInitials nvarchar(10) not null constraint DF_Person_MiddleInitials default '',
	LastName nvarchar(100) not null constraint DF_Person_LastName default 'Doe',
	constraint UC_Person_PK primary key clustered (PersonNumber),
	constraint U__Person_AK unique (FirstName, MiddleInitials, LastName)
);

drop table if exists AttendanceType;
create table AttendanceType (
	AttendanceCode char(1) not null,
	Name nvarchar(100) not null,
	constraint UC_AttendanceType_PK primary key clustered (AttendanceCode)
);

drop table if exists ClassDate;
create table ClassDate (
	ClassDate date not null,
	constraint UC_ClassDate_PK primary key clustered (ClassDate)
);

drop table if exists Attendance;
create table Attendance (
	PersonNumber int not null,
	ClassDate date not null,
	AttendanceType char(1) not null,
	constraint UC_Attendance_PK primary key clustered (PersonNumber, ClassDate, AttendanceType),
	constraint U__Attendance_AK unique nonclustered (ClassDate, PersonNumber, AttendanceType),
	constraint Person_records_Attendance_FK foreign key (PersonNumber) references Person (PersonNumber),
	constraint ClassDate_is_recorded_in_Attendance_FK foreign key (ClassDate) references ClassDate (ClassDate)
);

insert into AttendanceType (AttendanceCode, Name)
values
	('P', 'Present'),
	('A', 'Absent'),
	('E', 'Excused');

go

create or alter view Number
as
with
	L1 as (select 1 as A union all select 1),
	L2 as (select 1 as A from L1 a cross join L1 b),
	L3 as (select 1 as A from L2 a cross join L2 b),
	L4 as (select 1 as A from L3 a cross join L3 b),
	L5 as (select 1 as A from L4 a cross join L4 b),
	Numbers (Number) as (select row_number() over (order by (select 1)) - 1 from L5)
select Number
from Numbers;

go

insert into Person (FirstName, LastName) select top 2000 newid(), newid() from Number;

insert into ClassDate (ClassDate)
select dateadd(day, Number, cast('2020-01-01' as date))
from Number
where dateadd(day, Number, cast('2020-01-01' as date)) < cast('2023-12-31' as date);

insert into Attendance (PersonNumber, ClassDate, AttendanceType)
select PersonNumber, ClassDate, AttendanceType
from Person
cross apply ClassDate
outer apply (
	select abs(cast(cast(newid() as binary(8)) as bigint)) % 3 as Random
) d1
outer apply (
	select
		case Random
			when 0 then 'P'
			when 1 then 'A'
			when 2 then 'E'
		end as AttendanceType
) d2;

go

create or alter procedure GenReport
	@StartDate date,
	@EndDate date,
	@ViewName sysname
as

set nocount, xact_abort on;

begin try
	declare
		@SelectColumns nvarchar(max) = N'',
		@OuterApplies nvarchar(max) = N'';

	select @SelectColumns += N'
	   isnull([' +
	   cast(dateadd(day, Number, @StartDate) as varchar(20)) +
	   '], '''') as [' +
	   cast(dateadd(day, Number, @StartDate) as varchar(20)) +
	   ']' +
	   case when dateadd(day, Number, @StartDate) < @EndDate then ',' else '' end
	from Number
	where dateadd(day, Number, @StartDate) <= @EndDate;

	select @OuterApplies += N'
outer apply (
	select a.AttendanceType as [' + cast(dateadd(day, Number, @StartDate) as varchar(20)) + ']
	  from Attendance a with (forceseek, index (UC_Attendance_PK))
	 where a.PersonNumber = p.PersonNumber
	   and a.ClassDate = ''' + cast(dateadd(day, Number, @StartDate) as varchar(20)) + '''
) [d' + cast(dateadd(day, Number, @StartDate) as varchar(20)) + ']'
	from Number
	where dateadd(day, Number, @StartDate) <= @EndDate;

	declare @Query nvarchar(max) = N'/* This view was generated by a tool */
create or alter view %%ViewName%%
as
select p.FirstName,
	   p.LastName,%%SelectColumns%%
  from Person p%%OuterApplies%%;';

	set @Query = replace(@Query, '%%ViewName%%', @ViewName);
	set @Query = replace(@Query, '%%SelectColumns%%', @SelectColumns);
	set @Query = replace(@Query, '%%OuterApplies%%', @OuterApplies);

	begin transaction;
	exec sp_executesql @stmt = @Query;
	commit;
end try
begin catch
	if @@trancount != 0 rollback;
	throw;
end catch

go

create or alter procedure GenReportByMonth
	@StartDate date
as
set nocount, xact_abort on;

if datepart(day, @StartDate) != 1 throw 50000, N'Please specify a first-of-the-month date (e.g.: 2022-01-01).', 1;

declare @EndDate date = dateadd(day, -1, dateadd(month, 1, @StartDate));
declare @MonthName char(3) = substring(datename(month, @StartDate), 1, 3);
declare @Year int = datepart(year, @StartDate);
declare @ViewName sysname = 'AttendanceReport_' + cast(@Year as varchar(4)) + '_' + @MonthName;

exec GenReport @StartDate, @EndDate, @ViewName;

go

create or alter procedure GenReportByCalendarYearQuarter
	@StartDate date
as
set nocount, xact_abort on;

if datepart(day, @StartDate) != 1 throw 50000, N'Please specify a first-of-the-month date (e.g.: 2022-01-01).', 1;
if datepart(month, @StartDate) not in (1,4,7,10) throw 50000, N'Please specify a calendar year quarter start date (e.g.: 2022-01-01  2022-04-01, 2022-07-01, 2022-10-01).', 1;

declare @EndDate date = dateadd(day, -1, dateadd(month, 3, @StartDate));
declare @Year int = datepart(year, @StartDate);
declare @Quarter int = case datepart(month, @StartDate) when 1 then 1 when 4 then 2 when 7 then 3 when 10 then 4 end;
declare @ViewName sysname = 'AttendanceReport_' + cast(@Year as varchar(4)) + '_CY_Q' + cast(@Quarter as varchar(5));

exec GenReport @StartDate, @EndDate, @ViewName;

go

exec GenReportByMonth '2022-01-01';
exec GenReportByCalendarYearQuarter '2022-01-01';
exec GenReportByCalendarYearQuarter '2022-04-01';
exec GenReportByCalendarYearQuarter '2022-07-01';
exec GenReportByCalendarYearQuarter '2022-10-01';
go

select * from AttendanceReport_2022_Jan;
select * from AttendanceReport_2022_CY_Q1;