Student Attendance Report Month-wise SQL Query

Question

There are 3 tables -

Attendance (EnrollmentNo,SubjectCode,Date,Attendance)

Student (EnrollmentNo, RollNo), 

UserDetails(EnrollmentNo,FirstName,LastName).

Now what I want is to display the attendance month-wise taking Roll No, Name, dates as column and Student.RollNo, UserDetails.FirstName, UserDetails.LastName, Attendance.Attendance as the data for the columns respectively.

But the problem I am facing is how to generate date columns dynamically and put the attendance data in the respective date column.

Input - Startdate and Enddate

Expected Output -
-------------------------------------------------------
| Roll No |       Name       | 01-09-2018 | 01-12-2018|
-------------------------------------------------------
|    15   |   Suyash Gupta   |     1      |     0     |
-------------------------------------------------------
|    24   |  Himanshu Shukla |     2      |     2     |
-------------------------------------------------------
|    32   | Dhruv Raj Sirohi |     1      |     1     |
-------------------------------------------------------

This is my approach -

DECLARE @startdate date
DECLARE @enddate date

SET @startdate = convert(date,'01-09-2018')
SET @enddate = convert(date,'01-12-2018')

;with cte (@startdate, @enddate) as /*I don't know how to pass my date range 
                                    in cte() as this takes table column*/
(
    select 1
    union all
    select dateadd(dd, 1, 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
                left join Attendance A
                on D.startdate = A.Date
            ) x
           pivot 
           (
                count(startdate)
                for PivotDate in (' + @cols + ')
           ) p '

execute(@query)

Answer 1

This is running code and giving me the desired output. I want to thank Lobo, who corrected me, and everyone else who had put in the efforts to help me. Thank you all and stackoverflow who provided me the platform to query the problem I was facing.

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 2

you have some issues with your code, look at the differences in my code:

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
    @cols = STUFF((SELECT DISTINCT
            ',' + QUOTENAME(CONVERT(CHAR(10),
            startdate, 120))
        FROM cte
        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
                left join Attendance A
                on D.startdate = A.Date
            ) x
           pivot 
           (
                count(startdate)
                for PivotDate in (' + @cols + ')
           ) p '

EXECUTE (@query)

Answer 3

Do you mean something like this:

DECLARE @StartDate AS DATETIME, @EndDate AS DATETIME
SET @StartDate='2018-01-01'
SET @EndDate='2018-01-10'

SELECT Student.RollNo, Student.FirstName, Student.LastName, Attendance.Date
FROM Attendance,Student,UserDetails
WHERE Attendance.EnrollmentNo=Student.EnrollmentNo
AND UserDetails.EnrollmentNo=Student.EnrollmentNo
AND Attendance.[Date] between @StartDate and @EndDate

UPDATE

Based on what you said in the question maybe the following code is what you need:

SELECT Student.RollNo, Student.FirstName, Student.LastName, 
STUFF((SELECT  ','+CAST([DATE] AS VARCHAR(30))
FROM Attendance
WHERE Attendance.EnrollmentNo=Student.EnrollmentNo
AND Attendance.[Date] between @StartDate AND @EndDate
FOR XML PATH('')
), 1, 1, '') Dates
FROM Student,UserDetails
WHERE UserDetails.EnrollmentNo=Student.EnrollmentNo