Row-wise sum of dynamically generated columns in SQL

Question

This is the procedure of generating monthly attendance report of a class where I want to add one more column as "Total", which should contain sum of the attendance of a particular student between the dates, given as a parameter.

So what should I do to achieve that as simply doing sum(Attendance) will do the sum of attendance of all dates present in the table ?

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY
@startdate DATE,  
@enddate DATE,
@coursecode nvarchar(10),
@subjectcode nvarchar(10)

AS BEGIN

DECLARE @query as varchar(MAX);
declare @stmt nvarchar(max);
declare @stmt1 nvarchar(max);

with cte (startdate) as 
(
    select @startdate startdate
    union all 
    select dateadd(DD, 1, startdate) 
    from cte
    where startdate < @enddate
)

select @query = coalesce(@query, '') + 
              N',coalesce(MAX(CASE when A.[Date] = ''' + 
              cast(cte.startdate as nvarchar(20)) + 
              N''' THEN Convert(varchar(10),A.[Attendance]) end), ''NA'') ' + 
              quotename(convert(char(6), cte.startdate,106))
from cte
where datename(weekday, cte.startdate) <> 'Sunday';

set @query = 'Select S.RollNo AS [Roll No],Concat(FirstName,'' '',LastName) AS Name' + @query + ',sum(Attendance) Total
          from Attendance A, Student S, UserDetails U
          where A.EnrollmentNo=S.EnrollmentNo and S.EnrollmentNo=U.userID and A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode + '''
          and A.Date between ''' + @startdate + ' and ' + @enddate + '''
          Group By S.RollNo,U.FirstName,U.LastName';


Execute (@query)
END

Answer 1

Use Conditional Aggregation

sum(case when date between @startdate and @enddate then Attendance else 0 end)  

or Even better approach, filter the dates in where clause, also use sp_executesql to parameterize the dynamic query. Makes the query to look clean.

set @query = 'Select S.RollNo AS [Roll No],Concat(FirstName,'' '',LastName) AS Name' + @query + ',sum(Attendance) Total
              from Attendance A, Student S, UserDetails U
              where A.EnrollmentNo=S.EnrollmentNo and S.EnrollmentNo=U.userID and A.CourseCode= @coursecode and A.SubjectCode = @subjectcode 
              and A.[Date] between @startDate and @EndDate
              Group By S.RollNo,U.FirstName,U.LastName'


Execute sp_executesql @query, N'@coursecode varchar(100),@subjectcode varchar(100), @startDate date, @EndDate date',@coursecode,@subjectcode, @startDate, @EndDate