title: Student Attendance Report Month-wise SQL Query
body: <p>There are 3 tables - </p>
  
  <pre><code>Attendance (EnrollmentNo,SubjectCode,Date,Attendance)
  
  Student (EnrollmentNo, RollNo), 
  
  UserDetails(EnrollmentNo,FirstName,LastName).
  </code></pre>
  
  <p>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.</p>
  
  <p>But the problem I am facing is how to generate date columns dynamically and put the attendance data in the respective date column. </p>
  
  <pre><code>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     |
  -------------------------------------------------------
  </code></pre>
  
  <p>This is my approach - </p>
  
  <pre><code>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 &lt;= 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)
  </code></pre>