I am generating an attendance report in mysql, by pulling the data from 3 tables like student name and roll no from admissions table, Absent details from absentees table and i have created a calendar table for generating desired output i am able to generate output here is my database and the result i am generating [Plz look into this Attendance Report][2]

Now i want to display wherever 'SUN' occurs in Table as 'S' and also if there any holiday dates in the report, should show them as 'H'.

Could anyone tell me what should i do in my query to produce the expected result. [1]: http://sqlfiddle.com/#!2/62b91/1 [2]: http://sqlfiddle.com/#!2/62b91/14

I am generating an attendance report in mysql, by pulling the data from 3 tables like student name and roll no from admissions table, Absent details from absentees table and I have created a calendar table for generating desired output I am able to generate output here is my database and the result I am generating. SQL Fiddle

Now I want to display wherever 'SUN' occurs in Table as 'S' and also if there any holiday dates in the report, should show them as 'H'.

Could anyone tell me what should I do in my query to produce the expected result?

My data:

CREATE TABLE tbl_admission
    (`StudentName` varchar(5), `RollNo` int, `Class` int)
;
    
INSERT INTO tbl_admission
    (`StudentName`, `RollNo`, `Class`)
VALUES
    ('Naren', 1, 22),
    ('Srinu', 2, 22),
    ('Blah', 3, 22)
;

CREATE TABLE tbl_absentees
    (`Date` datetime, `Status` varchar(1), `RollNo` int, `Class` int)
;
    
INSERT INTO tbl_absentees
    (`Date`, `Status`, `RollNo`, `Class`)
VALUES
    ('2013-06-01 00:00:00', 'A', 1, 22),
    ('2013-06-02 00:00:00', 'A', 1, 22),
    ('2013-06-03 00:00:00', 'A', 1, 22),
    ('2013-06-04 00:00:00', 'A', 1, 22),
    ('2013-06-05 00:00:00', 'A', 1, 22),
    ('2013-06-06 00:00:00', 'A', 1, 22),
    ('2013-06-07 00:00:00', 'A', 1, 22),
    ('2013-06-08 00:00:00', 'A', 1, 22),
    ('2013-06-09 00:00:00', 'A', 1, 22),
    ('2013-06-10 00:00:00', 'A', 1, 22),
    ('2013-06-11 00:00:00', 'A', 1, 22),
    ('2013-06-12 00:00:00', 'A', 1, 22),
    ('2013-06-13 00:00:00', 'A', 1, 22),
    ('2013-06-14 00:00:00', 'A', 1, 22),
    ('2013-06-15 00:00:00', 'A', 1, 22),
    ('2013-06-16 00:00:00', 'A', 1, 22),
    ('2013-06-17 00:00:00', 'A', 1, 22),
    ('2013-06-18 00:00:00', 'A', 1, 22),
    ('2013-06-19 00:00:00', 'A', 1, 22),
    ('2013-06-20 00:00:00', 'A', 1, 22)
;

CREATE TABLE calendar
    (`Date` datetime)
;
    
INSERT INTO calendar
    (`Date`)
VALUES
    ('2013-06-01 00:00:00'),
    ('2013-06-02 00:00:00'),
    ('2013-06-03 00:00:00'),
    ('2013-06-04 00:00:00'),
    ('2013-06-05 00:00:00'),
    ('2013-06-06 00:00:00'),
    ('2013-06-07 00:00:00'),
    ('2013-06-08 00:00:00'),
    ('2013-06-09 00:00:00'),
    ('2013-06-10 00:00:00'),
    ('2013-06-11 00:00:00'),
    ('2013-06-12 00:00:00'),
    ('2013-06-13 00:00:00'),
    ('2013-06-14 00:00:00'),
    ('2013-06-15 00:00:00'),
    ('2013-06-16 00:00:00'),
    ('2013-06-17 00:00:00'),
    ('2013-06-18 00:00:00'),
    ('2013-06-19 00:00:00'),
    ('2013-06-20 00:00:00');

CREATE TABLE `tbl_holidays` (
  `id` int(11) NOT NULL auto_increment,
  `Hname` varchar(60) NOT NULL,
  `Htype` varchar(45) default NULL,
  `StartDate` date default NULL,
  `ToDate` date default NULL,
  `Descr` varchar(75) default NULL,
  `status` tinyint(4) default '1',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tbl_holidays` (`id`, `Hname`, `Htype`, `StartDate`, `ToDate`, `Descr`, `status`) VALUES (1,'Independence Day','Mandatory','2013-08-15','2013-08-15','It is Mandatory Holiday',1),(2,'Ramzan','Optional','2013-09-09','2013-09-09','It is Optional Holiday',1),(3,'UGADHI','Mandatory','2013-07-01','2013-07-02','efdrvb ',1);

My Current Code:

set session group_concat_max_len= 2048;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(date, '%a %d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-12';

SET @sql 
  = CONCAT('SELECT ca.studentname,
              ca.rollno,
              ca.class, ', @sql, ' 
            from
            (
              select c.date, a.studentname, a.rollno, a.class
              from calendar c
              cross join tbl_admission a
            ) ca
            left join tbl_absentees p
              on ca.rollno = p.rollno
              and ca.date = p.date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
            group by ca.studentname, ca.rollno, ca.class
            order by ca.rollno');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;