mysql-create-view-monthly-columns

month columns mysql





 CREATE VIEW `PAY_HISTORY_VIEW` AS select `payroll`.`employeenumber` AS `employeenumber`,`payroll`.`surname` AS `surname`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 1),`payroll`.`netPay`,0)) AS `last1`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 2),`payroll`.`netPay`,0)) AS `last2`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 3),`payroll`.`netPay`,0)) AS `last3`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 4),`payroll`.`netPay`,0)) AS `last4`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 5),`payroll`.`netPay`,0)) AS `last5`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 6),`payroll`.`netPay`,0)) AS `last6`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 7),`payroll`.`netPay`,0)) AS `last7`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 8),`payroll`.`netPay`,0)) AS `last8`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 9),`payroll`.`netPay`,0)) AS `last9`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 10),`payroll`.`netPay`,0)) AS `last10`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 11),`payroll`.`netPay`,0)) AS `last11`,
 sum(if((((month(curdate()) + (year(curdate()) * 12)) - (`payroll`.`mth` + (`payroll`.`year` * 12))) = 12),`payroll`.`netPay`,0)) AS `last12`,
 0 AS `recordid`,' ' AS `status` from `payroll` group by `payroll`.`employeenumber`,`payroll`.`surname` | utf8