Not able to display correct data using pivot table

Question

Table structure:

This is the data for which i want to use pivot table

i want to display the result as

Teacher Activity [2013-11-22] and so on The date field will display the count of the attendance.

Below is the code which i am using, result is coming as 0 under every date, which is not correct:

     DECLARE @temp AS TABLE(dates datetime)

        ;with cte (datelist, maxdate) as
        (
            select DATEADD(dd,-3,GETDATE()), GETDATE()
            union all
            select dateadd(dd, 1, datelist), maxdate
            from cte
            where datelist < maxdate
        )
        INSERT INTO @temp
        SELECT c.datelist
        FROM cte c 


        DECLARE @listdate nvarchar(MAX)  


        SELECT @listdate = (select(STUFF( (SELECT ', ' + QUOTENAME(convert(CHAR(10), dates, 120))                        
            from @temp ORDER BY dates asc                       
            FOR XML PATH('')),1, 1, '')))    

        print @listdate


        exec('(SELECT * INTO ##temptable from
        (select distinct teachername as Teacher,activityname AS Activity,memberid as attendance,
                QUOTENAME(convert(CHAR(10), attendancedate, 120)) AS DATES from
          tbteachertimelog inner join tbteacher on
        teacherid = tbteacher.id
        inner join tbactivity on tbactivity.id = tbteachertimelog.activityid
        left join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and 
        convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120)
        group by teachername,activityname,memberid,
        attendancedate
        ) p            
        PIVOT            
        (            
        count(attendance)            
        FOR DATES IN ('+@listdate+')) AS pvt            
        )            

        ')
alter table ##temptable add TotalStudents int,meetings int,total64 int



    update ##temptable set TotalStudents = 
    (SELECT SUM(memcount) FROM (select count(distinct memberid) as memcount from tbteachertimelog 
    inner join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and 
    convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120)
    where --teacherid = ##temptable.teacherid and tbteachertimelog.activityid = ##temptable.activityid
    --and
     tbattendance.attendancedate >= dateadd(dd,-7,getdate()) and  tbattendance.attendancedate <= getdate()
    group by convert(CHAR(10), tbattendance.attendancedate, 120)) x)

    update ##temptable set meetings = 
    (select count(distinct convert(CHAR(10), tbattendance.attendancedate, 120)) as dayscount from tbteachertimelog 
    inner join tbattendance on tbattendance.activityid = tbteachertimelog.activityid and 
    convert(CHAR(10), tbattendance.attendancedate, 120) = convert(CHAR(10), tbteachertimelog.date, 120)
    where teacherid = ##temptable.teacherid and tbteachertimelog.activityid = ##temptable.activityid)


    select * from ##temptable  

    drop table ##temptable

Answer 1

The problem is with the following line in your subquery:

QUOTENAME(convert(CHAR(10), attendancedate, 120)) AS DATES

The QUOTENAME is required when you are creating the list of dates as the column headers but since you added this to your subquery your dates in the table appear:

DATES
[2013-11-22]
[2013-11-23]
[2013-11-24]

But you want the dates being returned inside your subquery should be without the brackets:

DATES
2013-11-22
2013-11-23
2013-11-24

The PIVOT function is looking for dates that match your data but since you have the dates surrounded with square brackets you don't get any matches, as a result you are returning zeros for everything.

Your columns don't match your data because of the bracket -- for example:

Header     --                YourDate
2013-11-22 -- does not match [2013-11-22]
2013-11-23 -- does not match [2013-11-23]
2013-11-24 -- does not match [2013-11-24]

The query should be:

exec('
with data as 
(
  SELECT * 
  from
  (
    select distinct teachername as Teacher,
      activityname AS Activity,
      memberid as attendance,
      convert(CHAR(10), attendancedate, 120) AS DATES 
    from tbteachertimelog 
    inner join tbteacher 
      on teacherid = tbteacher.id
    inner join tbactivity 
      on tbactivity.id = tbteachertimelog.activityid
    inner join tbattendance 
     on tbattendance.activityid = tbactivity.id
  ) p            
  PIVOT            
  (            
    count(attendance)            
    FOR DATES IN ('+@listdate+')
  ) AS pvt            
)            
select * 
from data 
')