Ignore Sunday while generating student attendance report in SQL

This code gives me the attendance report between two dates passed as a parameter. I will pass dates respective of the month selected from the C# code.

But I want to skip Sundays while generating the attendance report. How can I achieve this?

DECLARE @startdate date = '20180109';
DECLARE @enddate date = '20180112';
DECLARE @cols as varchar(2000);
DECLARE @query as varchar(MAX);

WITH cte (startdate)
AS 
(SELECT
        @startdate AS startdate
    UNION ALL
    SELECT
        DATEADD(DAY, 1, startdate) AS startdate
    FROM cte
    WHERE startdate < @enddate
)
select c.startdate
into #tempDates
from cte c

SELECT
    @cols = STUFF((SELECT DISTINCT
            ',' + QUOTENAME(CONVERT(CHAR(10),
            startdate, 120))
        FROM #tempDates
        FOR XML PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)')
    , 1, 1, '')

SET @query = 'SELECT RollNo,FirstName,LastName, ' + @cols + ' from 
             (
                select S.RollNo,U.FirstName,U.LastName,
                D.startdate,
                convert(CHAR(10), startdate, 120) PivotDate
                from #tempDates D,Attendance A, Student S, UserDetails U
                where D.startdate = A.Date and A.EnrollmentNo=S.EnrollmentNo and A.EnrollmentNo=U.userID
            ) x
           pivot 
           (
                count(startdate)
                for PivotDate in (' + @cols + ')
           ) p '

EXECUTE (@query)
drop table #tempDates

Answer

How about changing #TempDates?

select c.startdate
into #tempDates
from cte c
where datename(weekday, c.startdate) <> 'Sunday';

This assumes that your internationalization settings are set to “English”.

Advertisement

Answer