title: Mysql query to print attendance sheet
body: <p>Please some one help me tI have two Tables in my MYSQL database,</p>
  
  <p>the first table - (<strong>Employee Table)</strong> consists of </p>
  
  <pre><code>EmployeeNo| EmployeeName
  </code></pre>
  
  <p>the second table - <strong>(Attendance table)</strong> consists of  </p>
  
  <pre><code>DATE | TIME | STATUS| EmployeeNo
  </code></pre>
  
  <p>and a calendar table that holds only date for every month </p>
  
  <p>I want to generate attendance sheet similar to this 
  <a href="https://s27.postimg.org/g2uepwhmr/attendance.png" rel="nofollow noreferrer">Click here</a></p>
  
  <p>I end up writing the following SQL, but it gives me syntax error</p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your 
          MySQL server version for the right syntax to use near 'from (select cal.calendarDate, 
          emp.first_name,emp.nu' at line 2
  </code></pre>
  
  <p>and my query , </p>
  
  <pre><code>    SET @sql = NULL;
      SELECT
      GROUP_CONCAT(DISTINCT
      CONCAT(
        'max(CASE WHEN calemp.calendarDate = ''',date_format(calendarDate, '%Y-%m-%d'),''' THEN coalesce(att.inorout, ''P'') END) AS `',date_format(calendarDate, '%Y-%m-%d'), '`'
      )
        ) INTO @sql
       FROM yearly_date_calendar
       where calendarDate &gt;= '2016-11-01'
       and calendarDate &lt;= '2016-11-30';
  
      SET @sql = CONCAT('SELECT calemp.first_name,calemp.nurse_code,',@sql,'
              from
              (
                select cal.calendarDate,emp.first_name,emp.nurse_code
                from yearly_date_calendar cal
                cross join syscare_caregiver emp
              ) calemp
              left join syscare_employee_attendance att
                on calemp.nurse_code = att.emp_code
                and calemp.calendarDate = att.attendance_date
              where calemp.calendarDate&gt;=''2016-11-01''
                and calemp.calendarDate &lt;= ''2016-11-30''
              group by calemp.first_name, calemp.nurse_code,calemp.calendarDate
        ');
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  </code></pre>