Mysql – Pivot Table – Columns to Rows –

As I suggested in my comments, when you are altering the code for a dynamic SQL solution I would always suggest that you write the SQL query first so you can get the JOINs and logic correct, then convert it to dynamic SQL.

The first thing you need to do to get the result is to incorporate the tbl_Holidays. I would start the query by joining it to you calendar table, then you can easily identify what dates are holidays:

select c.date, 
  case when h.startdate is null then 'N' else 'Y' end holiday
from calendar c
left join tbl_holidays h
  on c.date = h.startdate;

Once you have a list of the dates and holiday, then just like your previous query, you will cross join this result to tbl_admission:

select ch.date, ch.holiday, a.studentname, a.rollno, a.class
from
(
  select c.date, 
    case when h.startdate is null then 'N' else 'Y' end holiday
  from calendar c
  left join tbl_holidays h
    on c.date = h.startdate
) ch
cross join tbl_admission a;

See Demo. This will give you a list of all of dates, the holidays and the students that you can then join to your tbl_absentees to determine who was present/absent on each day.

Now, since you want to change the display value in the result based on if the day is a Sunday or a Holiday, then you will need to alter your original CASE expression. The new code will be similar to the following:

max(CASE WHEN ca.date = '2013-06-01' then
      case 
          when ca.holiday = 'Y'  then 'H'
          when dayname(ca.date) = 'SUNDAY' then 'S' 
          else coalesce(p.status, 'P') end END) AS `SAT 01`

The new CASE expression does 4 things:

1. Checks the date
2. If the date is a holiday, then display an H
3. If the date is a Sunday, then display an S
4. if it is not a holiday or a Sunday, then display the A/P that you initially had.

Putting the entire code together you will have a static version of the query that looks like:

SELECT ca.studentname,
  ca.rollno,
  ca.class,
  max(CASE WHEN ca.date = '2013-06-01' then
          case 
              when ca.holiday = 'Y'  then 'H' 
              when dayname(ca.date) = 'SUNDAY' then 'S' 
              else coalesce(p.status, 'P') end END) AS `SAT 01`,
  max(CASE when ca.date = '2013-06-02' then
        case
          when ca.holiday = 'Y'  then 'H'
          when dayname(ca.date) = 'SUNDAY' then 'S' 
          else coalesce(p.status, 'P') end END) AS `SUN 02`,
  max(CASE WHEN ca.date = '2013-06-03' then
        case 
          when ca.holiday = 'Y'  then 'H' 
          when dayname(ca.date) = 'SUNDAY' then 'S' 
          else coalesce(p.status, 'P') end END) AS `MON 03`
from
(
  select ch.date, ch.holiday, a.studentname, a.rollno, a.class
  from
  (
    select c.date, 
      case when h.startdate is null then 'N' else 'Y' end holiday
    from calendar c
    left join tbl_holidays h
      on c.date = h.startdate
  ) ch
  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;

See SQL Fiddle with Demo. Now that you have a static version with working logic you can convert into your dynamic SQL code:

set session group_concat_max_len= 5000;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN 
            case 
              when ca.holiday = ''Y'' then ''H'' 
              when dayname(ca.date)=''Sunday'' then ''S''
              else coalesce(p.status, ''P'') end 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 ch.date, ch.holiday, a.studentname, a.rollno, a.class
              from
              (
                select c.date, 
                  case when h.startdate is null then ''N'' else ''Y'' end holiday
                from calendar c
                left join tbl_holidays h
                  on c.date = h.startdate
              ) ch
              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;

See SQL Fiddle with Demo. A few side notes, since you are adding significantly more characters in the sql string you might have to increase the session length for group_concat_max_len. Also, you could reduce the number of characters by using MySQL's abbreviated IF() logic to replace the CASE expressions.

This new version of the code will give you a result:

| STUDENTNAME | ROLLNO | CLASS | SAT 01 | SUN 02 | MON 03 | TUE 04 | WED 05 | THU 06 | FRI 07 | SAT 08 | SUN 09 | MON 10 | TUE 11 | WED 12 |
--------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |      A |      S |      H |      A |      A | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|       Srinu |      2 |    22 |      P |      S |      H |      P |      P | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|        Blah |      3 |    22 |      P |      S |      H |      P |      P | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).