Pivot query issue in the attendance system report

I am building a attendance system where I have to show the attendance of a whole class for a particular month.

So I have tried the below method but I am not getting the desired output.

I know I am doing something wrong in the pivot query as I am new to this type of query and I built this query with the help from the other posts.

Can someone guide me what I am doing wrong here and what should I do to achieve the desired result ?

Attendance Table Sample Data -

Attendance

Student Table Sample Data -

Student

UserDetails Table Sample Data -

UserDetails

Current Output -

Current Output

I want to combine those duplicate records.

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

AS BEGIN

DECLARE @cols as varchar(2000);
DECLARE @cols_select as varchar(MAX);
DECLARE @col varchar(20)
DECLARE @cols_copy varchar(100)
DECLARE @query as varchar(MAX);

WITH cte (startdate)
AS 
(SELECT
        @startdate AS startdate
    UNION ALL
    SELECT
        DATEADD(DD, 1, startdate) AS startdate
    FROM cte
    WHERE startdate < @enddate
)
select c.startdate
into #tempDates
from cte c
where datename(weekday, c.startdate) <> 'Sunday';

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

select @cols_copy = replace(@cols, ',', '')

select @cols_select = SUBSTRING(@cols_select, 2, len(@cols_select) - 1)


SET @query = 'SELECT RollNo,Concat(FirstName,'' '',LastName) AS Name, ' + dbo.fn_convert_cols(@cols) + ' from 
             (
                select S.RollNo,U.FirstName,U.LastName,
                D.startdate,A.Attendance,
                convert(CHAR(6), startdate, 106) PivotDate
                from #tempDates D,Attendance A, Student S, UserDetails U
                where convert(CHAR(6), D.startdate, 106) = convert(CHAR(6), A.Date, 106) and A.EnrollmentNo=S.EnrollmentNo and S.EnrollmentNo=U.userID and A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode + '''
            ) x
           pivot 
           (
                max(Attendance)
                for PivotDate in (' + @cols + ')
           ) p '

EXECUTE (@query)
drop table #tempDates
END

1 answer

  • answered 2018-01-14 12:10 shA.t

    I think you can use old approach of pivot like this - with some tunes -;

    ;with cte (startdate) as 
    (
        select @startdate startdate
        union all 
        select dateadd(DD, 1, startdate) 
        from cte
        where startdate < @enddate
    )
    select @query = coalesce(@query, '') + 
                  N',max(case when A.[Date] = ''' + 
                  cast(cte.startdate as nvarchar(20)) + 
                  N''' then A.Attendance end) ' + 
                  quotename(convert(char(6), cte.startdate,106))
    from cte
    where datename(weekday, cte.startdate) <> 'Sunday';
    
    set @query = N'select S.RollNo, U.FirstName, U.LastName' + 
                @query +
               N' from Attendance A join Student S on A.EnrollmentNo = S.EnrollmentNo join UserDetails U on A.EnrollmentNo = U.userID' +
               N' where A.CourseCode = ''' + @coursecode + N''' and A.SubjectCode = ''' + @subjectcode + N'''' +
               N' group by S.RollNo, U.FirstName, U.LastName';
    
    exec sp_executesql @query