0

I have a simple database (using MySql) with which I need to generate a report. I have a table that schedule_plan which are submitted by users.

this my column in my table schedule_plan

and my table userinfo which contain name ,age dll

So I need a report which displays the days as columns as well as the names from a userinfo table.

Report
+------+--------+--------+--------+-------+
| Name |    1   |    2   |   3    | until day 30/31  |
+------+--------+--------+--------+-------+
| Bob  |     ON |      ON |    ON | ...   |
| Joe  |    OFF |      ON |   OFF | ...   |
| Jim  |     ON |      ON |    ON | ...   |

if absence_code in schedule_plan is null then ON if not null OFF

Piyush Gupta
  • 2,181
  • 3
  • 13
  • 28
Hardian Z
  • 1
  • 2
  • What did u try?........Refer this http://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns and update query also which one u tried... – Piyush Gupta Jun 22 '16 at 05:08
  • i think don't work for me because my query must MAX(IF(absence_code is null, 'ON','Off')) as Day 1 that MAX func i think useless for me. – Hardian Z Jun 22 '16 at 05:49

1 Answers1

0
create table attendance
(user_id int,attendance_code varchar(1),dte date)
*/
truncate table attendance;
insert into attendance values
(1,'a','2016-01-01'),
(1,null,'2016-01-02'),
(1,'a','2016-01-03'),
(1,'a','2016-01-04'),
(1,'a','2016-01-05'),
(2,'a','2016-01-01'),
(2,'a','2016-01-02'),
(2,null,'2016-01-03'),
(2,null,'2016-01-04'),
(2,'a','2016-01-05')
;
select a.user_id, 
max(case when day(a.dte) = 1 then case when a.attendance_code is not null then 'On' else 'Off' end end) as day1,
max(case when day(a.dte) = 2 then case when a.attendance_code is not null then 'On' else 'Off' end end) as day2,
max(case when day(a.dte) = 3 then case when a.attendance_code is not null then 'On' else 'Off' end end) as day3,
max(case when day(a.dte) = 4 then case when a.attendance_code is not null then 'On' else 'Off' end end) as day4,
max(case when day(a.dte) = 5 then case when a.attendance_code is not null then 'On' else 'Off' end end) as day5
from attendance a
group by a.user_id;
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • so i must write day(a.dte) until 31, how about if just 28 or 30 i mean uknown number day – Hardian Z Jun 22 '16 at 08:41
  • Or 29 or intra-month. If having inappropriate like 29-31 in month 2 is unacceptable to you then you need to build a sql statment and run it using dynamic sql based on the solution I provided. – P.Salmon Jun 22 '16 at 08:59