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;
Best Answer
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 youcalendar
table, then you can easily identify what dates are holidays: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
: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:
The new CASE expression does 4 things:
H
S
A
/P
that you initially had.Putting the entire code together you will have a static version of the query that looks like:
See SQL Fiddle with Demo. Now that you have a static version with working logic you can convert into your dynamic SQL code:
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 abbreviatedIF()
logic to replace theCASE
expressions.This new version of the code will give you a result: