I would go for the first option, mostly because I believe that less columns are better.
But also:
First of all, the first option would only require an insert when a student is absent, while the second would, first, have an insert for each student at each month, and then for each student that is absent, it would require to update the row each time the student is absent.
The second option would however give a good matrix directly in the database which days a student is absent, but that would be easily achieved through PHP.
Also, having one row per day a student is absent, allows for better statistics when it comes to reporting.
For instance, check how many students that were absent during one month:
select month(date), count(*) from attendance where is_absent = 1 group by month(date);
compared to (here I assume that d1..d31 is an integer with either 1 or 0)
select month(date), sum(d1+d2+d3+d4+d5..+d31) from attendance group by month(date);
Or, if you have a student
table as well (which I assume you will have) and you want to see which students were not absent:
select student_name
from students
where student_id not in (select student_id from attendance)
Compared to:
select student_name
from students s
join attendance a on s.student_id = a.student_id
where (d1+d2+d3+d4+d5..+d31) = 0
Finally, since you wanted to store the reason for the student to be absent, it means for option 2 you would either, have one more row for each reason or have one more column for each day which stores the reason, since otherwise you could only store one reason per month, unless you store the reason as a concatenated string in the column which would require a separator and thus limit the characters you put in as reason.
However, in the end it is more of a personal preference, since with correctly created indexes, I doubt that you would have to worry about the performance, but depending on the amount of absent students, option 2 would be using more storage than option 1 (number of columns * rows * size of the data types)
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:
- Checks the date
- If the date is a holiday, then display an
H
- If the date is a Sunday, then display an
S
- 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) |
Best Answer
This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.
My first suggestion would be to determine if you have a
calendar
table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:This will make it possible for you to generate a list of all of the dates that you want to display.
Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your
tbl_admission
and thecalendar
table:See Demo. Once you have this list, then you can use a LEFT JOIN to your existing
tbl_absentees
table to get the result:See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:
See SQL Fiddle with Demo. Both of these queries will give a result similar to: