Using MySQL Pivot Table Queries for a Monthly Attendance Report

I have used pivot query for monthly report..

My table is like this:

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;

For monthly attendance report, I have used this query:

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

So my question is, isn't this query too long? Is there any alternative to this query to display monthly attendance report ?

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?

UPDATE:

Here is a screenshot which explain what type of report I want:

---

No idea if your original query would work but something like this would be simpler:

select DAY(date),count(*) from attendance where MONTH(date) = 2 group by DAY(date);

That should get the attendance for February. The output would be:

+-----------+----------+
| DAY(date) | count(*) |
+-----------+----------+
|      18   |        1 |
|      19   |        2 |
+-----------+----------+

---

First, create a help table:

CREATE TABLE days
( d TINYINT PRIMARY KEY
) ;

INSERT INTO days
VALUES
(0), (1), (2), ..., (31) ;

Then you can try this. It will return less columns than your query though:

SELECT 
    a.empID
  , GROUP_CONCAT( CASE WHEN a.attStatus IS NULL
                           THEN 'N'
                           ELSE a.attStatus
                  END
                  ORDER BY dates.dateCheck)
    AS days
  , COUNT( CASE WHEN a.attStatus='P' THEN 1 END )
    AS totalP
FROM
    ( SELECT monthStart + INTERVAL d DAY
             AS dateCheck
      FROM days
        CROSS JOIN
          ( SELECT '2011-07-01' AS monthStart 
          ) AS m 
      WHERE MONTH(monthStart + INTERVAL d DAY) = MONTH(monthStart)
    ) AS dates
  LEFT JOIN attendance AS a
    ON a.`date` = dates.dateCheck
WHERE a.deptID = 1 
GROUP BY a.empID