SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Transform statement's series in a single statement.

    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.

    Code:
    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:
    Code:
    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:

    1) SUM of hours month jan, feb and mar is wrong, 465970 instead of 595581;
    2) the output of alias `z` is wrong... because I need 12,811;
    3) 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.


    Code:
    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');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    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?
    Code:
    mysql> 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 <= 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) >= '12' then
          set this = concat( 1+left(this,4), '01' );
        else
          set this = concat( left(this,4), if(right(this,2)<'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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •