title: Using MySQL Pivot Table Queries for a Monthly Attendance Report
body: <p>I have used pivot query for monthly report..</p>
  
  <p>My table is like this:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `attendance` (
    `empID` int(11) NOT NULL,
    `date` date NOT NULL,
    `deptID` int(11) NOT NULL,
    `attStatus` varchar(3) NOT NULL,
    PRIMARY KEY (`empID`,`date`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>For monthly attendance report, I have used this query:</p>
  
  <pre><code>SELECT empID,
    GROUP_CONCAT(if(DAY(`date`) = 1, `attStatus`, NULL)) AS 'day1', 
    GROUP_CONCAT(if(DAY(`date`) = 2, `attStatus`, NULL)) AS 'day2', 
    GROUP_CONCAT(if(DAY(`date`) = 3, `attStatus`, NULL)) AS 'day3', 
    GROUP_CONCAT(if(DAY(`date`) = 4, `attStatus`, NULL)) AS 'day4', 
    GROUP_CONCAT(if(DAY(`date`) = 5, `attStatus`, NULL)) AS 'day5', 
    GROUP_CONCAT(if(DAY(`date`) = 6, `attStatus`, NULL)) AS 'day6', 
    GROUP_CONCAT(if(DAY(`date`) = 7, `attStatus`, NULL)) AS 'day7', 
    GROUP_CONCAT(if(DAY(`date`) = 8, `attStatus`, NULL)) AS 'day8', 
    GROUP_CONCAT(if(DAY(`date`) = 9, `attStatus`, NULL)) AS 'day9', 
    GROUP_CONCAT(if(DAY(`date`) = 10, `attStatus`, NULL)) AS 'day10',
    GROUP_CONCAT(if(DAY(`date`) = 11, `attStatus`, NULL)) AS 'day11', 
    GROUP_CONCAT(if(DAY(`date`) = 12, `attStatus`, NULL)) AS 'day12', 
    GROUP_CONCAT(if(DAY(`date`) = 13, `attStatus`, NULL)) AS 'day13', 
    GROUP_CONCAT(if(DAY(`date`) = 14, `attStatus`, NULL)) AS 'day14', 
    GROUP_CONCAT(if(DAY(`date`) = 15, `attStatus`, NULL)) AS 'day15', 
    GROUP_CONCAT(if(DAY(`date`) = 16, `attStatus`, NULL)) AS 'day16', 
    GROUP_CONCAT(if(DAY(`date`) = 17, `attStatus`, NULL)) AS 'day17', 
    GROUP_CONCAT(if(DAY(`date`) = 18, `attStatus`, NULL)) AS 'day18', 
    GROUP_CONCAT(if(DAY(`date`) = 19, `attStatus`, NULL)) AS 'day19', 
    GROUP_CONCAT(if(DAY(`date`) = 20, `attStatus`, NULL)) AS 'day20', 
    GROUP_CONCAT(if(DAY(`date`) = 21, `attStatus`, NULL)) AS 'day21', 
    GROUP_CONCAT(if(DAY(`date`) = 22, `attStatus`, NULL)) AS 'day22', 
    GROUP_CONCAT(if(DAY(`date`) = 23, `attStatus`, NULL)) AS 'day23', 
    GROUP_CONCAT(if(DAY(`date`) = 24, `attStatus`, NULL)) AS 'day24', 
    GROUP_CONCAT(if(DAY(`date`) = 25, `attStatus`, NULL)) AS 'day25', 
    GROUP_CONCAT(if(DAY(`date`) = 26, `attStatus`, NULL)) AS 'day26', 
    GROUP_CONCAT(if(DAY(`date`) = 27, `attStatus`, NULL)) AS 'day27', 
    GROUP_CONCAT(if(DAY(`date`) = 28, `attStatus`, NULL)) AS 'day28', 
    GROUP_CONCAT(if(DAY(`date`) = 29, `attStatus`, NULL)) AS 'day29', 
    GROUP_CONCAT(if(DAY(`date`) = 30, `attStatus`, NULL)) AS 'day30',  
    GROUP_CONCAT(if(DAY(`date`) = 31, `attStatus`, NULL)) AS 'day31', 
    COUNT(if(`attStatus`='P', `attStatus`, NULL)) AS 'totalP'
  FROM `attendance`
  WHERE deptID=1 AND `date` BETWEEN '2011-07-01' AND '2011-07-31'
  GROUP BY empID
  </code></pre>
  
  <p>So my question is, isn't this query too long?
  Is there any alternative to this query to display monthly attendance report ?</p>
  
  <p>If this query is okay then for those months which have 30 days in it, result for 31 days is displayed. Is there any solution to this?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Here is a screenshot which explain what type of report I want:</p>
  
  <p><img src="https://i.stack.imgur.com/MndlL.jpg" alt="enter image description here"></p>