How to perform operation on multiple rows in mysql

Question

I have a shift schedule table for employee wise shift to every month. In this table have a total of 34 fields.

Table header is:

EmpNo |Month|year|Day1|Day2|Day3|Day4|Day5|Day6|Day7|Day8|Day9|Day10|Day11|Day12|Day13|Day14|Day15|Day16|Day17|Day18|Day19|Day20|Day21|Day22|Day23|Day24|Day25|Day26|Day27|Day28|Day29|Day30|Day31

Table data:

101|06|2018|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1
102|06|2018|A1|A1|H|A1|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1
103|06|2018|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1

HERE A1 is shift code, WO is week-off and H is the holiday.

I need to calculate the total count of the week off and the holiday of employee month wise.

You might want to redesign this table. Doesn't look sustainable — Himanshu Bhardwaj, Aug 21 '18 at 05:41
I agree with the above comment, and you should instead have a single date column, with which you do things like sum over a given month. — Tim Biegeleisen, Aug 21 '18 at 05:45
Sorry, if a month has less then 31 days then it will remain null extra days fields. — ajay rathod, Aug 21 '18 at 06:48

score 0 · Answer 1 · answered Aug 21 '18 at 07:42

You could do something like this How to count items in comma separated list MySQL where length and replace are used to figure out the number of occurances. In your case it's a bit more complex (but not much) and you have to concat the days and cater for nulls - I suggest adding an ifnull test to every day and substituting a dummy value 'x' perhaps.

drop table if exists t;
create table t(empid varchar(3),month varchar(2), year varchar(4),
day1 varchar(2), day2 varchar(2) ,day3 varchar(2),day4 varchar(2), day5 varchar(2), day6 varchar(2),
day7 varchar(2), day8 varchar(2) ,day9 varchar(2),day10 varchar(2), day11 varchar(2), day12 varchar(2),
day13 varchar(2), day14 varchar(2), day15 varchar(2),day16 varchar(2), day17 varchar(2), day18 varchar(2),
day19 varchar(2), day20 varchar(2), day21 varchar(2),day22 varchar(2), day23 varchar(2), day24 varchar(2),
day25 varchar(2), day26 varchar(2), day27 varchar(2),day28 varchar(2), day29 varchar(2), day30 varchar(2));
#day31 varchar(2));
insert into t values
('101','06','2018','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1'),
('102','06','2018','A1','A1','H','A1','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1'),
('103','06','2018','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1');

select empid,month,year,
         length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) len,
         length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'H','')) h,
                ( length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'WO',''))
                    ) /2  wo,

                 length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'H','')) +
                ( length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'WO',''))
                    ) /2   off
from t;

+-------+-------+------+------+------+--------+---------+
| empid | month | year | len  | h    | wo     | off     |
+-------+-------+------+------+------+--------+---------+
| 101   | 06    | 2018 |   58 |    2 | 8.0000 | 10.0000 |
| 102   | 06    | 2018 |   58 |    2 | 8.0000 | 10.0000 |
| 103   | 06    | 2018 |   59 |    1 | 8.0000 |  9.0000 |
+-------+-------+------+------+------+--------+---------+
3 rows in set (0.00 sec)

How to perform operation on multiple rows in mysql

1 Answers1