Transform statement's series in a single statement

cms9651 · March 15, 2012, 8:57pm

Hi all, your help would be very appreciated.
I’m sorry if I write so frequently in this forum.

I need transform this statement’s series in a single statement.


set @jansum=(select sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e)
             from tbl_my_flight_zone_hours
             where Months=1
             and Years=2011
             and left(my_Flight_zone,2)='TO');

set @febsum=(select sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e)
             from tbl_my_flight_zone_hours
             where Months=2
             and Years=2011
             and left(my_Flight_zone,2)='TO');

set @marsum=(select sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e)
             from tbl_my_flight_zone_hours
             where Months=3
             and Years=2011
             and left(my_Flight_zone,2)='TO');

set @marsum=@marsum*DAYOFMONTH(CURDATE())/DAYOFMONTH(LAST_DAY(CURDATE()));

set @Infort=( SELECT COUNT(*)
             FROM `tbl_my_flight_zone`
             WHERE `my_Flight_zone` LIKE '%TO%'
             AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE())-1,1)
             AND ADDDATE(CURDATE(),INTERVAL - 1 YEAR) );

set @res = (( (@Infort) / (@jansum+@febsum+@marsum) * 953.8 ) * 1000 );

select @jansum, @febsum, @marsum, (@jansum+@febsum+@marsum), @Infort, @res;

+---------+---------+-----------+---------------------------+---------+--------+
| @jansum | @febsum | @marsum   | (@jansum+@febsum+@marsum) | @Infort | @res   |
+---------+---------+-----------+---------------------------+---------+--------+
| 232771  | 237014  | 125796.29 | 595581.290322580000000000 |       8 | 12.811 |
+---------+---------+-----------+---------------------------+---------+--------+
1 row in set

Well… I tried this alternative join solution:


SELECT
       Infort
     , Flight
     , Sum(Hrs)*DAYOFMONTH(CURDATE())/DAYOFMONTH(LAST_DAY(CURDATE()))*953.8*1000 As Calc
     , (sums.hrs)
     , (((Infort)/Sum(Hrs)*DAYOFMONTH(CURDATE())/DAYOFMONTH(LAST_DAY(CURDATE()))*953.8)*1000) As z
FROM (
  SELECT
       COUNT(*) As Infort
     , LEFT(z.`my_Flight_zone`,2) As Flight
     , z.Months
     , SUM(z.my_number_of_flight_hours_w + z.my_number_of_flight_hours_e) As Hrs
  FROM tbl_my_flight_zone_hours z
     JOIN tbl_my_flight_zone y
                          ON (
                             LEFT(z.`my_Flight_zone`,2) = LEFT(y.`my_Flight_zone`,2)
                             AND (y.`myDates` BETWEEN MAKEDATE(YEAR(CURDATE())-1,1)
                             AND ADDDATE(CURDATE(),INTERVAL -1 YEAR))
                             )
  WHERE 1
     GROUP BY
          Flight
        , Months
) sums
GROUP BY Flight;

+--------+--------+----------------------+---------+--------------+
| Infort | Flight | Calc                 | hrs     | z            |
+--------+--------+----------------------+---------+--------------+
|     24 | CM     | 433963616129.03137   | 465970  | 11.779276161 |
|    136 | TO     | 10811129778580.64448 | 1862168 | 2.679101129  |
+--------+--------+----------------------+---------+--------------+
2 rows in set

With this single statement I have this problem’s for Flight TO:

SUM of hours month jan, feb and mar is wrong, 465970 instead of 595581;
the output of alias z is wrong… because I need 12,811;
the output of Infort alias is wrong 136 instead of 8.

Can you help me?

Very difficult situation for me, thanks for all and I hope your help.


SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_my_flight_zone`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_my_flight_zone`;
CREATE TABLE `tbl_my_flight_zone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `myDates` date DEFAULT NULL,
  `my_Flight_zone` varchar(4) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `my_Flight_zone` (`my_Flight_zone`)
) ENGINE=MyISAM AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_my_flight_zone
-- ----------------------------
INSERT INTO tbl_my_flight_zone VALUES ('1', '2012-03-06', 'TO5Q');
INSERT INTO tbl_my_flight_zone VALUES ('2', '2012-02-17', 'LA7M');
INSERT INTO tbl_my_flight_zone VALUES ('3', '2012-02-17', 'LA7M');
INSERT INTO tbl_my_flight_zone VALUES ('4', '2012-02-16', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('5', '2012-02-06', 'TOVM');
INSERT INTO tbl_my_flight_zone VALUES ('6', '2012-02-03', 'LA4O');
INSERT INTO tbl_my_flight_zone VALUES ('7', '2012-02-03', 'LA2M');
INSERT INTO tbl_my_flight_zone VALUES ('8', '2012-02-01', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('9', '2012-01-31', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('10', '2012-01-31', 'TO5P');
INSERT INTO tbl_my_flight_zone VALUES ('11', '2012-01-31', 'TOSR');
INSERT INTO tbl_my_flight_zone VALUES ('12', '2012-01-25', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('13', '2012-01-17', 'TOVM');
INSERT INTO tbl_my_flight_zone VALUES ('14', '2012-01-16', 'LASR');
INSERT INTO tbl_my_flight_zone VALUES ('15', '2011-12-22', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('16', '2011-12-21', 'TO1M');
INSERT INTO tbl_my_flight_zone VALUES ('17', '2011-12-19', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('18', '2011-12-19', 'TO6O');
INSERT INTO tbl_my_flight_zone VALUES ('19', '2011-12-15', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('20', '2011-12-13', 'TO6O');
INSERT INTO tbl_my_flight_zone VALUES ('21', '2011-12-13', 'LA7O');
INSERT INTO tbl_my_flight_zone VALUES ('22', '2011-12-12', 'CMVM');
INSERT INTO tbl_my_flight_zone VALUES ('23', '2011-11-30', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('24', '2011-11-30', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('25', '2011-11-25', 'SAVM');
INSERT INTO tbl_my_flight_zone VALUES ('26', '2011-11-16', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('27', '2011-11-10', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('28', '2011-10-16', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('29', '2011-10-12', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('30', '2011-10-04', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('31', '2011-10-04', 'TO6M');
INSERT INTO tbl_my_flight_zone VALUES ('32', '2011-10-03', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('33', '2011-09-28', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('34', '2011-09-26', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('35', '2011-09-21', 'LA4O');
INSERT INTO tbl_my_flight_zone VALUES ('36', '2011-09-12', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('37', '2011-09-09', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('38', '2011-09-09', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('39', '2011-09-09', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('40', '2011-09-08', 'LA4P');
INSERT INTO tbl_my_flight_zone VALUES ('41', '2011-09-06', 'CM4M');
INSERT INTO tbl_my_flight_zone VALUES ('42', '2011-09-05', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('43', '2011-08-30', 'LA6N');
INSERT INTO tbl_my_flight_zone VALUES ('44', '2011-08-28', 'TOVM');
INSERT INTO tbl_my_flight_zone VALUES ('45', '2011-08-24', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('46', '2011-08-23', 'TO6M');
INSERT INTO tbl_my_flight_zone VALUES ('47', '2011-08-22', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('48', '2011-08-20', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('49', '2011-08-19', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('50', '2011-08-10', 'SA1Q');
INSERT INTO tbl_my_flight_zone VALUES ('51', '2011-08-08', 'LAER');
INSERT INTO tbl_my_flight_zone VALUES ('52', '2011-08-06', 'LA4O');
INSERT INTO tbl_my_flight_zone VALUES ('53', '2011-07-22', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('54', '2011-07-13', 'SA1O');
INSERT INTO tbl_my_flight_zone VALUES ('55', '2011-07-11', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('56', '2011-07-07', 'TO6O');
INSERT INTO tbl_my_flight_zone VALUES ('57', '2011-07-06', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('58', '2011-07-06', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('59', '2011-06-27', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('60', '2011-06-23', 'SA1N');
INSERT INTO tbl_my_flight_zone VALUES ('61', '2011-06-23', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('62', '2011-06-13', 'TO5R');
INSERT INTO tbl_my_flight_zone VALUES ('63', '2011-06-08', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('64', '2011-06-07', 'CMSR');
INSERT INTO tbl_my_flight_zone VALUES ('65', '2011-05-25', 'LA4P');
INSERT INTO tbl_my_flight_zone VALUES ('66', '2011-05-24', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('67', '2011-05-23', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('68', '2011-05-19', 'LA7P');
INSERT INTO tbl_my_flight_zone VALUES ('69', '2011-05-11', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('70', '2011-05-09', 'CM4N');
INSERT INTO tbl_my_flight_zone VALUES ('71', '2011-05-05', 'TO5Q');
INSERT INTO tbl_my_flight_zone VALUES ('72', '2011-04-07', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('73', '2011-03-30', 'SAER');
INSERT INTO tbl_my_flight_zone VALUES ('74', '2011-03-20', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('75', '2011-03-15', 'TO1P');
INSERT INTO tbl_my_flight_zone VALUES ('76', '2011-03-13', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('77', '2011-03-09', 'SA1N');
INSERT INTO tbl_my_flight_zone VALUES ('78', '2011-03-05', 'SA1P');
INSERT INTO tbl_my_flight_zone VALUES ('79', '2011-03-01', 'TO6O');
INSERT INTO tbl_my_flight_zone VALUES ('80', '2011-03-01', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('81', '2011-02-26', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('82', '2011-02-24', 'LA4P');
INSERT INTO tbl_my_flight_zone VALUES ('83', '2011-02-23', 'LASR');
INSERT INTO tbl_my_flight_zone VALUES ('84', '2011-02-23', 'TOSR');
INSERT INTO tbl_my_flight_zone VALUES ('85', '2011-02-22', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('86', '2011-02-15', 'TO5P');
INSERT INTO tbl_my_flight_zone VALUES ('87', '2011-02-08', 'LA4Q');
INSERT INTO tbl_my_flight_zone VALUES ('88', '2011-01-25', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('89', '2011-01-25', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('90', '2011-01-24', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('91', '2010-12-28', 'SA1O');
INSERT INTO tbl_my_flight_zone VALUES ('92', '2010-12-27', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('93', '2010-12-27', 'LA4O');
INSERT INTO tbl_my_flight_zone VALUES ('94', '2010-12-20', 'LA7M');
INSERT INTO tbl_my_flight_zone VALUES ('95', '2010-12-14', 'TO1O');
INSERT INTO tbl_my_flight_zone VALUES ('96', '2010-12-02', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('97', '2010-11-24', 'TO6N');
INSERT INTO tbl_my_flight_zone VALUES ('98', '2010-11-18', 'TO5P');
INSERT INTO tbl_my_flight_zone VALUES ('99', '2010-11-15', 'LA2M');
INSERT INTO tbl_my_flight_zone VALUES ('100', '2010-11-15', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('101', '2010-11-12', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('102', '2010-11-12', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('103', '2010-11-08', 'SAER');
INSERT INTO tbl_my_flight_zone VALUES ('104', '2010-10-21', 'LA4P');
INSERT INTO tbl_my_flight_zone VALUES ('105', '2010-10-20', 'TO1M');
INSERT INTO tbl_my_flight_zone VALUES ('106', '2010-10-18', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('107', '2010-09-21', 'LA4N');
INSERT INTO tbl_my_flight_zone VALUES ('108', '2010-09-18', 'LA4Q');
INSERT INTO tbl_my_flight_zone VALUES ('109', '2010-09-07', 'TOVM');
INSERT INTO tbl_my_flight_zone VALUES ('110', '2010-09-06', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('111', '2010-08-25', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('112', '2010-08-13', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('113', '2010-08-10', 'CM4O');
INSERT INTO tbl_my_flight_zone VALUES ('114', '2010-08-09', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('115', '2010-08-04', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('116', '2010-08-02', 'SA1Q');
INSERT INTO tbl_my_flight_zone VALUES ('117', '2010-07-30', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('118', '2010-07-30', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('119', '2010-07-29', 'TO5R');
INSERT INTO tbl_my_flight_zone VALUES ('120', '2010-07-29', 'CM6N');
INSERT INTO tbl_my_flight_zone VALUES ('121', '2010-07-26', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('122', '2010-07-23', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('123', '2010-07-20', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('124', '2010-07-18', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('125', '2010-07-14', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('126', '2010-07-07', 'TO6M');
INSERT INTO tbl_my_flight_zone VALUES ('127', '2010-07-06', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('128', '2010-07-01', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('129', '2010-06-30', 'TO5Q');
INSERT INTO tbl_my_flight_zone VALUES ('130', '2010-06-30', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('131', '2010-06-06', 'CM6N');
INSERT INTO tbl_my_flight_zone VALUES ('132', '2010-05-24', 'LA7M');
INSERT INTO tbl_my_flight_zone VALUES ('133', '2010-05-20', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('134', '2010-05-20', 'SA1Q');
INSERT INTO tbl_my_flight_zone VALUES ('135', '2010-05-18', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('136', '2010-05-14', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('137', '2010-05-12', 'CM4M');
INSERT INTO tbl_my_flight_zone VALUES ('138', '2010-05-07', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('139', '2010-05-07', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('140', '2010-05-03', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('141', '2010-04-29', 'TO5R');
INSERT INTO tbl_my_flight_zone VALUES ('142', '2010-04-23', 'SAER');
INSERT INTO tbl_my_flight_zone VALUES ('143', '2010-04-20', 'CM4N');
INSERT INTO tbl_my_flight_zone VALUES ('144', '2010-04-16', 'CM6M');
INSERT INTO tbl_my_flight_zone VALUES ('145', '2010-04-14', 'TO5M');
INSERT INTO tbl_my_flight_zone VALUES ('146', '2010-04-14', 'SA1Q');
INSERT INTO tbl_my_flight_zone VALUES ('147', '2010-04-14', 'CM4P');
INSERT INTO tbl_my_flight_zone VALUES ('148', '2010-04-08', 'TOVM');
INSERT INTO tbl_my_flight_zone VALUES ('149', '2010-03-22', 'TO1Q');
INSERT INTO tbl_my_flight_zone VALUES ('150', '2010-03-19', 'CMSR');
INSERT INTO tbl_my_flight_zone VALUES ('151', '2010-03-18', 'TO1O');
INSERT INTO tbl_my_flight_zone VALUES ('152', '2010-03-11', 'CM4M');
INSERT INTO tbl_my_flight_zone VALUES ('153', '2010-03-11', 'CM4M');
INSERT INTO tbl_my_flight_zone VALUES ('154', '2010-03-11', 'LA7P');
INSERT INTO tbl_my_flight_zone VALUES ('155', '2010-03-11', 'LA2N');
INSERT INTO tbl_my_flight_zone VALUES ('156', '2010-03-05', 'TOSR');
INSERT INTO tbl_my_flight_zone VALUES ('157', '2010-03-04', 'LA4N');
INSERT INTO tbl_my_flight_zone VALUES ('158', '2010-02-26', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('159', '2010-02-23', 'SAER');
INSERT INTO tbl_my_flight_zone VALUES ('160', '2010-02-19', 'CM4M');
INSERT INTO tbl_my_flight_zone VALUES ('161', '2010-02-12', 'SA1N');
INSERT INTO tbl_my_flight_zone VALUES ('162', '2010-02-10', 'CM6P');
INSERT INTO tbl_my_flight_zone VALUES ('163', '2010-01-21', 'TO1N');
INSERT INTO tbl_my_flight_zone VALUES ('164', '2010-01-21', 'LA7M');
INSERT INTO tbl_my_flight_zone VALUES ('165', '2010-01-20', 'TO5P');
INSERT INTO tbl_my_flight_zone VALUES ('166', '2010-01-18', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('167', '2010-01-18', 'SA1M');
INSERT INTO tbl_my_flight_zone VALUES ('168', '2010-01-12', 'SA1Q');
INSERT INTO tbl_my_flight_zone VALUES ('169', '2010-01-10', 'LA7O');
INSERT INTO tbl_my_flight_zone VALUES ('170', '2010-01-04', 'TO1O');
INSERT INTO tbl_my_flight_zone VALUES ('171', '2009-12-18', 'TO5N');
INSERT INTO tbl_my_flight_zone VALUES ('172', '2009-11-20', 'TO5R');
INSERT INTO tbl_my_flight_zone VALUES ('173', '2009-11-19', 'TO6O');
INSERT INTO tbl_my_flight_zone VALUES ('174', '2009-11-06', 'LA6N');
INSERT INTO tbl_my_flight_zone VALUES ('175', '2009-11-04', 'TO1P');
INSERT INTO tbl_my_flight_zone VALUES ('176', '2009-09-23', 'SA1P');
INSERT INTO tbl_my_flight_zone VALUES ('177', '2009-09-21', 'LA4Q');
INSERT INTO tbl_my_flight_zone VALUES ('178', '2009-09-16', 'CM6O');
INSERT INTO tbl_my_flight_zone VALUES ('179', '2009-08-05', 'CMSR');
INSERT INTO tbl_my_flight_zone VALUES ('180', '2009-07-22', 'LA2M');
INSERT INTO tbl_my_flight_zone VALUES ('181', '2009-06-18', 'LA7N');
INSERT INTO tbl_my_flight_zone VALUES ('182', '2009-06-18', 'CM4O');
INSERT INTO tbl_my_flight_zone VALUES ('183', '2009-06-09', 'SA1N');
INSERT INTO tbl_my_flight_zone VALUES ('184', '2009-06-03', 'CM4N');
INSERT INTO tbl_my_flight_zone VALUES ('185', '2009-05-13', 'LA4P');
INSERT INTO tbl_my_flight_zone VALUES ('186', '2009-04-18', 'TO1M');
INSERT INTO tbl_my_flight_zone VALUES ('187', '2009-03-23', 'LA4O');
INSERT INTO tbl_my_flight_zone VALUES ('188', '2008-10-15', 'SA1O');
INSERT INTO tbl_my_flight_zone VALUES ('189', '2008-08-07', 'LA4M');
INSERT INTO tbl_my_flight_zone VALUES ('190', '2008-06-16', 'LAER');
INSERT INTO tbl_my_flight_zone VALUES ('191', '2008-05-06', 'TO5Q');
INSERT INTO tbl_my_flight_zone VALUES ('192', '2007-12-12', 'TO6M');
INSERT INTO tbl_my_flight_zone VALUES ('193', '2007-05-30', 'TO6N');
INSERT INTO tbl_my_flight_zone VALUES ('194', '2006-09-18', 'LA6M');
INSERT INTO tbl_my_flight_zone VALUES ('195', '2010-01-08', 'TOER');
INSERT INTO tbl_my_flight_zone VALUES ('196', '2010-02-01', 'TO5N');

-- ----------------------------
-- Table structure for `tbl_my_flight_zone_hours`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_my_flight_zone_hours`;
CREATE TABLE `tbl_my_flight_zone_hours` (
  `my_Flight_zone` varchar(100) DEFAULT NULL,
  `my_number_of_flight_hours_w` int(10) DEFAULT NULL,
  `my_number_of_flight_hours_e` int(10) DEFAULT NULL,
  `Months` int(1) DEFAULT NULL,
  `Years` int(4) DEFAULT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=239 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Records of tbl_my_flight_zone_hours
-- ----------------------------
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4M', '10975', '7996', '1', '2011', '1');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4N', '7621', '5832', '1', '2011', '2');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4O', '9570', '6290', '1', '2011', '3');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4P', '12869', '8953', '1', '2011', '4');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6M', '18105', '15270', '1', '2011', '5');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6N', '13608', '14517', '1', '2011', '6');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6O', '12462', '11522', '1', '2011', '7');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6P', '14131', '12154', '1', '2011', '8');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMSR', '0', '16490', '1', '2011', '9');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMER', '0', '27967', '1', '2011', '10');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMVM', '0', '5553', '1', '2011', '11');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMSA', '0', '1100', '1', '2011', '12');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4M', '10891', '7642', '2', '2011', '13');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4N', '8353', '5973', '2', '2011', '14');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4O', '9932', '6836', '2', '2011', '15');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM4P', '12070', '10221', '2', '2011', '16');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6M', '18430', '16607', '2', '2011', '17');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6N', '12417', '14941', '2', '2011', '18');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6O', '12338', '12210', '2', '2011', '19');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CM6P', '13618', '12457', '2', '2011', '20');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMSR', '0', '17637', '2', '2011', '21');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMER', '0', '27045', '2', '2011', '22');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMVM', '0', '6451', '2', '2011', '23');
INSERT INTO tbl_my_flight_zone_hours VALUES ('CMSA', '0', '1096', '2', '2011', '24');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9035', '5822', '1', '2011', '25');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '8499', '5384', '1', '2011', '26');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '8915', '6918', '1', '2011', '27');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5135', '4838', '1', '2011', '28');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '10284', '7045', '1', '2011', '29');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '8906', '6358', '1', '2011', '30');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '18330', '10123', '1', '2011', '31');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6851', '5136', '1', '2011', '32');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3487', '3769', '1', '2011', '33');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8415', '5146', '1', '2011', '34');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '9087', '5938', '1', '2011', '35');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '3986', '3840', '1', '2011', '36');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '6434', '5492', '1', '2011', '37');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '20732', '1', '2011', '38');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '267', '22208', '1', '2011', '39');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5601', '1', '2011', '40');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '790', '1', '2011', '41');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '8911', '6221', '2', '2011', '42');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '8477', '5865', '2', '2011', '43');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '8904', '7020', '2', '2011', '44');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5431', '4965', '2', '2011', '45');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '9749', '7475', '2', '2011', '46');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '8563', '6754', '2', '2011', '47');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '18669', '9750', '2', '2011', '48');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6613', '5152', '2', '2011', '49');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3555', '4036', '2', '2011', '50');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8714', '5847', '2', '2011', '51');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '9401', '6175', '2', '2011', '52');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '3924', '3649', '2', '2011', '53');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '6971', '5844', '2', '2011', '54');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '21370', '2', '2011', '55');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '329', '22396', '2', '2011', '56');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5574', '2', '2011', '57');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '710', '2', '2011', '58');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '20532', '10911', '3', '2011', '69');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '17337', '9073', '4', '2011', '70');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '21301', '11064', '5', '2011', '71');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '19662', '10277', '6', '2011', '72');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '18504', '8967', '7', '2011', '73');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '16811', '8324', '8', '2011', '74');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '19378', '10768', '9', '2011', '75');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '20215', '10603', '10', '2011', '76');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '18925', '10539', '11', '2011', '77');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5N', '18369', '9688', '12', '2011', '78');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9912', '6554', '3', '2011', '79');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9011', '5613', '4', '2011', '80');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '11168', '6405', '5', '2011', '81');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '10419', '6166', '6', '2011', '82');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9453', '5365', '7', '2011', '83');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9452', '5139', '8', '2011', '84');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '10135', '6011', '9', '2011', '85');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '10487', '6181', '10', '2011', '86');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9588', '6136', '11', '2011', '87');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1M', '9671', '5649', '12', '2011', '88');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9597', '6172', '3', '2011', '89');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '7958', '5313', '4', '2011', '90');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9573', '6458', '5', '2011', '91');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9171', '6068', '6', '2011', '92');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '8773', '5322', '7', '2011', '93');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9140', '5358', '8', '2011', '94');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9213', '6173', '9', '2011', '95');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9523', '6121', '10', '2011', '96');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '9784', '6205', '11', '2011', '97');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1N', '8881', '5387', '12', '2011', '98');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '9643', '7810', '3', '2011', '99');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '8461', '6655', '4', '2011', '100');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '10257', '8115', '5', '2011', '101');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '9385', '7771', '6', '2011', '102');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '8685', '6688', '7', '2011', '103');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '9144', '7061', '8', '2011', '104');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '9152', '7683', '9', '2011', '105');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '9970', '7969', '10', '2011', '106');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '10001', '7377', '11', '2011', '107');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1O', '10111', '6853', '12', '2011', '108');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '6132', '4935', '3', '2011', '109');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5349', '4334', '4', '2011', '110');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5953', '5442', '5', '2011', '111');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5900', '4887', '6', '2011', '112');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5822', '4586', '7', '2011', '113');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '4924', '4264', '8', '2011', '114');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5202', '4935', '9', '2011', '115');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '6479', '4916', '10', '2011', '116');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5685', '4889', '11', '2011', '117');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1P', '5615', '4138', '12', '2011', '118');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '11008', '8200', '3', '2011', '119');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '9691', '6456', '4', '2011', '120');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '12008', '8256', '5', '2011', '121');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '11283', '7523', '6', '2011', '122');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '10855', '6910', '7', '2011', '123');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '9268', '6877', '8', '2011', '124');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '11035', '7719', '9', '2011', '125');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '11829', '7830', '10', '2011', '126');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '11226', '7887', '11', '2011', '127');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO1Q', '10884', '7209', '12', '2011', '128');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9791', '6895', '3', '2011', '129');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '8181', '5532', '4', '2011', '130');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '10225', '7307', '5', '2011', '131');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9886', '6987', '6', '2011', '132');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9168', '6524', '7', '2011', '133');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '8799', '6128', '8', '2011', '134');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9929', '7385', '9', '2011', '135');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '10076', '6986', '10', '2011', '136');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9302', '6904', '11', '2011', '137');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5M', '9368', '6136', '12', '2011', '138');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '7669', '5452', '3', '2011', '139');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6450', '4578', '4', '2011', '140');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '7435', '5842', '5', '2011', '141');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '7065', '5611', '6', '2011', '142');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6419', '4797', '7', '2011', '143');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '5902', '4970', '8', '2011', '144');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6704', '5705', '9', '2011', '145');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '7667', '5702', '10', '2011', '146');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '6968', '5713', '11', '2011', '147');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5P', '7367', '5282', '12', '2011', '148');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4265', '4227', '3', '2011', '149');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3769', '3565', '4', '2011', '150');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4529', '4408', '5', '2011', '151');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4126', '4033', '6', '2011', '152');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3540', '3669', '7', '2011', '153');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3344', '3345', '8', '2011', '154');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4080', '3970', '9', '2011', '155');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4314', '4053', '10', '2011', '156');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '4120', '3929', '11', '2011', '157');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5Q', '3815', '3717', '12', '2011', '158');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '9214', '6109', '3', '2011', '159');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '7411', '4628', '4', '2011', '160');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '9453', '6193', '5', '2011', '161');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '9259', '5424', '6', '2011', '162');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8379', '5592', '7', '2011', '163');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8404', '5013', '8', '2011', '164');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '9126', '6015', '9', '2011', '165');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '9357', '5814', '10', '2011', '166');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8696', '5807', '11', '2011', '167');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO5R', '8826', '5147', '12', '2011', '168');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10608', '6663', '3', '2011', '169');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '8751', '5481', '4', '2011', '170');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '11183', '6857', '5', '2011', '171');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10688', '6617', '6', '2011', '172');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10155', '6027', '7', '2011', '173');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '9933', '6058', '8', '2011', '174');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10435', '6730', '9', '2011', '175');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10979', '6578', '10', '2011', '176');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '10281', '6406', '11', '2011', '177');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6M', '11153', '6318', '12', '2011', '178');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4438', '4499', '3', '2011', '179');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4040', '3742', '4', '2011', '180');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4692', '4567', '5', '2011', '181');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4429', '4059', '6', '2011', '182');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '3872', '3743', '7', '2011', '183');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4042', '3821', '8', '2011', '184');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4439', '4373', '9', '2011', '185');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4498', '4407', '10', '2011', '186');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4206', '4283', '11', '2011', '187');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6N', '4308', '4213', '12', '2011', '188');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7017', '6015', '3', '2011', '189');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '6147', '4498', '4', '2011', '190');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '8138', '6430', '5', '2011', '191');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7464', '5933', '6', '2011', '192');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '6992', '5242', '7', '2011', '193');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7132', '5701', '8', '2011', '194');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7560', '6263', '9', '2011', '195');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7582', '6064', '10', '2011', '196');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7310', '5919', '11', '2011', '197');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TO6O', '7710', '5594', '12', '2011', '198');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '327', '24889', '3', '2011', '199');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '305', '21293', '4', '2011', '200');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '188', '24908', '5', '2011', '201');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '127', '23342', '6', '2011', '202');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '21554', '7', '2011', '203');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '21212', '8', '2011', '204');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '23829', '9', '2011', '205');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '24266', '10', '2011', '206');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '23871', '11', '2011', '207');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOER', '0', '21194', '12', '2011', '208');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '809', '3', '2011', '209');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '683', '4', '2011', '210');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '851', '5', '2011', '211');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '763', '6', '2011', '212');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '571', '7', '2011', '213');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '582', '8', '2011', '214');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '857', '9', '2011', '215');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '737', '10', '2011', '216');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '781', '11', '2011', '217');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSA', '0', '691', '12', '2011', '218');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '23223', '3', '2011', '219');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '19066', '4', '2011', '220');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '23784', '5', '2011', '221');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '21397', '6', '2011', '222');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '19514', '7', '2011', '223');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '19043', '8', '2011', '224');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '22919', '9', '2011', '225');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '22814', '10', '2011', '226');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '22806', '11', '2011', '227');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOSR', '0', '19999', '12', '2011', '228');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '6463', '3', '2011', '229');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5472', '4', '2011', '230');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '6698', '5', '2011', '231');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5679', '6', '2011', '232');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5380', '7', '2011', '233');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5257', '8', '2011', '234');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5683', '9', '2011', '235');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5489', '10', '2011', '236');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5703', '11', '2011', '237');
INSERT INTO tbl_my_flight_zone_hours VALUES ('TOVM', '0', '5492', '12', '2011', '238');

cms9651 · March 17, 2012, 10:02am

Hi there.

I try use for my problem the Stored Procedure (sproc) in MySQL and I have fix differents problems.

But I can not the last operation:


statement #1

SELECT
(
(
(
	SELECT
		COUNT(*) As Infort
	FROM
		`tbl_my_flight_zone`
	WHERE
		`my_Flight_zone` LIKE '%TO%'
	AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
)
 /
(result) * 953.8
) * 1000
) x;

Because I can not relationship this statement #2 with statement #1:


statement #2

(
	SELECT
       sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) q1
      ,sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())) q2
      ,(result-(sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e))+
               (sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())))) z
	FROM
		tbl_my_flight_zone_hours
	WHERE 1
	AND	Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND LEFT(my_Flight_zone, 2)= 'TO'
);

Can you help me?

mysql&gt; SET @result = 0;
CALL sproc(
	CONCAT(YEAR(MAKEDATE(YEAR(CURDATE()) - 1, 1)),'0',MONTH(MAKEDATE(YEAR(CURDATE()) - 1, 1)))
,	CONCAT(YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
, CASE WHEN MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR)) BETWEEN 1 AND 9 THEN '0' ELSE '' END
,	MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))),'TO',@result);
SELECT @result;
Query OK, 0 rows affected

+--------+-------------+-------------+
| q1     | q2          | z           |
+--------+-------------+-------------+
| 259979 | 142569.1290 | 612354.1290 |
+--------+-------------+-------------+
1 row in set

+--------+
| Infort |
+--------+
|      8 |
+--------+
1 row in set

+----------+
| x        |
+----------+
| 10.45556 |
+----------+
1 row in set

Query OK, 0 rows affected

+-------------+
| @result     |
+-------------+
| 729764.0000 |
+-------------+
1 row in set


BEGIN
  declare this char(6);
  set this=ymstart, result=0;
  while this &lt;= ymstop do
    set result = result +
                 (select sum(my_number_of_flight_hours_e+my_number_of_flight_hours_w)
                  from tbl_my_flight_zone_hours
                  where years=left(this,4) and `months`=right(this,2) and left(my_flight_zone,2)=flightspec
                 );
    if right(this,2) &gt;= '12' then
      set this = concat( 1+left(this,4), '01' );
    else
      set this = concat( left(this,4), if(right(this,2)&lt;'09','0',''), 1+right(this,2) );
    end if;
  end while;


(
	SELECT
       sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) q1
      ,sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())) q2
      ,(result-(sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e))+
               (sum(my_number_of_flight_hours_w + my_number_of_flight_hours_e) *
                DAYOFMONTH(CURDATE()) / DAYOFMONTH(LAST_DAY(CURDATE())))) z
	FROM
		tbl_my_flight_zone_hours
	WHERE 1
	AND	Months = MONTH(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND `years`= YEAR(ADDDATE(CURDATE(), INTERVAL - 1 YEAR))
	AND LEFT(my_Flight_zone, 2)= 'TO'
);



	SELECT
		COUNT(*) As Infort
	FROM
		`tbl_my_flight_zone`
	WHERE
		`my_Flight_zone` LIKE '%TO%'
	AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR);



SELECT
(
(
(
	SELECT
		COUNT(*) As Infort
	FROM
		`tbl_my_flight_zone`
	WHERE
		`my_Flight_zone` LIKE '%TO%'
	AND `myDates` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
	AND ADDDATE(CURDATE(), INTERVAL - 1 YEAR)
)
 /
(result) * 953.8
) * 1000
) x;

END