How to change values on specific days for report generated from mysql

Question

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;

What do you mean by you want to highlight Sundays? What is the desired result of the query? My suggestion would be to write the query without the dynamic sql first, then convert it so you can get the logic correct. — Taryn, Commented Jul 31, 2013 at 14:28
Thank you @bluefeet for your response.And sorry for my unclear post. — Narendar_CH, Commented Aug 1, 2013 at 4:58
In my post we are generating a result that shows attendance report for 1 month/between the 2 dates as 1 2 3 4 5 --- 31 with status as A/L/P. in the result we are getting Day names with day as MON-1,TUE-2 -----. So now I want to show where ever the Name SUN Occurs I want to Highlight or set status as 'Sun' and also i have a holidays table. By pulling the holidays Dates into this report and show their status in the report as 'H'. Simply in the report, for suppose THU-15 is holiday if we generate report for 2013-08-01 to 2013-08-31, so i want to show 'H' on THU-15. — Narendar_CH, Commented Aug 1, 2013 at 5:10
this is modified code sqlfiddle.com/#!2/62b91/14 pl look into this — Narendar_CH, Commented Aug 1, 2013 at 7:09

Taryn · Accepted Answer · 2013-08-02 12:27:39Z

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) |

@Narendar_CH please don't badger users to look at other questions. If you've written an interesting question, people will find it. — Aaron Bertrand, Commented Aug 2, 2013 at 14:21
How Can i Select Count of all P's S's and H's in the above result @bluefeet — Narendar_CH, Commented Aug 22, 2013 at 12:24
@Narendar_CH did Bluefeet's answer solve your original problem? If so, please accept her answer and ask a new question. This site is not meant to serve as a discussion forum. Thanks. — Aaron Bertrand, Commented Aug 22, 2013 at 12:48

Stack Exchange Network

How to change values on specific days for report generated from mysql

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
or ask your own question.

Hot Network Questions

How to change values on specific days for report generated from mysql

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
or ask your own question.