--disable_query_log DROP TABLE IF EXISTS `RawData`; CREATE TABLE `RawData` ( `rowid` bigint(20) DEFAULT NULL, `parent_org_struct_id` int(11) DEFAULT NULL, `org_struct_id` int(11) DEFAULT NULL, `org_struct_name` varchar(100) DEFAULT NULL, `tasks_closed` int(11) DEFAULT NULL, `tasks_closed_outside_sla` int(11) DEFAULT NULL, `tasks_nps_promoter` int(11) DEFAULT NULL, `promoter_percent` float DEFAULT NULL, `tasks_nps_neutral` int(11) DEFAULT NULL, `neutral_percent` float DEFAULT NULL, `tasks_nps_detractor` int(11) DEFAULT NULL, `detractor_percent` float DEFAULT NULL, `nps_score` float DEFAULT NULL, `count_01` int(11) DEFAULT NULL, `count_02` int(11) DEFAULT NULL, `count_03` int(11) DEFAULT NULL, `count_04` int(11) DEFAULT NULL, `count_05` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8; INSERT INTO `RawData` VALUES (22364454717488109,101,1001,'Team 1',67,18,15,22.3881,2,2.98507,50,74.6269,-52.2388,0,0,0,0,0),(22364454717488110,101,1002,'Team 2',18,2,2,11.1111,0,0,16,88.8889,-77.7778,0,0,0,0,0),(22364454717488111,101,1003,'Team 3',50,14,6,12,1,2,43,86,-74,0,0,0,0,0),(22364454717488112,101,1004,'Team 4',21,9,2,9.52381,1,4.7619,18,85.7143,-76.1905,0,0,0,0,0),(22364454717488113,101,1005,'Team 5',15,6,0,0,3,20,12,80,-80,0,0,0,0,0),(22364454717488114,101,1007,'Team 7',49,13,5,10.2041,3,6.12245,41,83.6735,-73.4694,0,0,0,0,0),(22364454717488115,101,1009,'Team 9',48,9,13,27.0833,0,0,35,72.9167,-45.8333,0,0,0,0,0),(22364454717488116,101,1010,'Team 10',18,4,3,16.6667,1,5.55556,14,77.7778,-61.1111,0,0,0,0,0),(22364454717488117,102,1011,'Team 11',16,4,3,18.75,0,0,13,81.25,-62.5,0,0,0,0,0),(22364454717488118,102,1012,'Team 12',51,13,13,25.4902,0,0,38,74.5098,-49.0196,0,0,0,0,0),(22364454717488119,102,1013,'Team 13',66,22,13,19.697,3,4.54545,50,75.7576,-56.0606,0,0,0,0,0),(22364454717488120,102,1014,'Team 14',11,1,2,18.1818,1,9.09091,8,72.7273,-54.5455,0,0,0,0,0),(22364454717488121,102,1015,'Team 15',16,1,2,12.5,1,6.25,13,81.25,-68.75,0,0,0,0,0),(22364454717488122,102,1016,'Team 16',71,12,10,14.0845,0,0,61,85.9155,-71.831,0,0,0,0,0),(22364454717488123,102,1017,'Team 17',96,25,17,17.7083,3,3.125,76,79.1667,-61.4583,0,0,0,0,0),(22364454717488124,102,1018,'Team 18',53,15,5,9.43396,1,1.88679,47,88.6792,-79.2453,0,0,0,0,0),(22364454717488125,102,1019,'Team 19',87,17,17,19.5402,1,1.14943,69,79.3103,-59.7701,0,0,0,0,0),(22364454717488126,102,1020,'Team 20',29,8,3,10.3448,1,3.44828,25,86.2069,-75.8621,0,0,0,0,0),(22364454717488127,103,1021,'Team 21',40,13,6,15,2,5,32,80,-65,0,0,0,0,0),(22364454717488128,103,1022,'Team 22',32,7,3,9.375,2,6.25,27,84.375,-75,0,0,0,0,0),(22364454717488129,103,1023,'Team 23',20,6,4,20,0,0,16,80,-60,0,0,0,0,0),(22364454717488130,103,1024,'Team 24',19,6,2,10.5263,0,0,17,89.4737,-78.9474,0,0,0,0,0),(22364454717488131,103,1026,'Team 26',47,18,13,27.6596,1,2.12766,33,70.2128,-42.5532,0,0,0,0,0),(22364454717488132,103,1027,'Team 27',18,5,4,22.2222,1,5.55556,13,72.2222,-50,0,0,0,0,0),(22364454717488133,103,1028,'Team 28',49,15,10,20.4082,0,0,39,79.5918,-59.1837,0,0,0,0,0),(22364454717488134,103,1030,'Team 30',49,13,6,12.2449,1,2.04082,42,85.7143,-73.4694,0,0,0,0,0),(22364454717488135,104,1031,'Team 31',30,6,8,26.6667,0,0,22,73.3333,-46.6667,0,0,0,0,0),(22364454717488136,104,1032,'Team 32',39,7,9,23.0769,1,2.5641,29,74.359,-51.2821,0,0,0,0,0),(22364454717488137,104,1033,'Team 33',32,14,4,12.5,2,6.25,26,81.25,-68.75,0,0,0,0,0),(22364454717488138,104,1034,'Team 34',15,5,3,20,0,0,12,80,-60,0,0,0,0,0),(22364454717488139,104,1035,'Team 35',19,3,1,5.26316,1,5.26316,17,89.4737,-84.2105,0,0,0,0,0),(22364454717488140,104,1036,'Team 36',31,6,6,19.3548,1,3.22581,24,77.4194,-58.0645,0,0,0,0,0),(22364454717488141,104,1037,'Team 37',31,4,3,9.67742,1,3.22581,27,87.0968,-77.4194,0,0,0,0,0),(22364454717488142,104,1038,'Team 38',13,2,3,23.0769,0,0,10,76.9231,-53.8462,0,0,0,0,0),(22364454717488143,104,1039,'Team 39',18,5,2,11.1111,0,0,16,88.8889,-77.7778,0,0,0,0,0),(22364454717488144,104,1040,'Team 40',53,15,9,16.9811,3,5.66038,41,77.3585,-60.3774,0,0,0,0,0),(22364454717488145,105,1041,'Team 41',30,10,7,23.3333,0,0,23,76.6667,-53.3333,0,0,0,0,0),(22364454717488146,105,1042,'Team 42',40,9,8,20,0,0,32,80,-60,0,0,0,0,0),(22364454717488147,105,1043,'Team 43',61,20,12,19.6721,7,11.4754,42,68.8525,-49.1803,0,0,0,0,0),(22364454717488148,105,1044,'Team 44',37,11,5,13.5135,0,0,32,86.4865,-72.973,0,0,0,0,0),(22364454717488149,105,1045,'Team 45',51,11,12,23.5294,4,7.84314,35,68.6274,-45.098,0,0,0,0,0),(22364454717488150,105,1047,'Team 47',26,8,7,26.9231,2,7.69231,17,65.3846,-38.4615,0,0,0,0,0),(22364454717488151,105,1048,'Team 48',32,6,9,28.125,1,3.125,22,68.75,-40.625,0,0,0,0,0),(22364454717488152,105,1049,'Team 49',42,10,8,19.0476,0,0,34,80.9524,-61.9048,0,0,0,0,0),(22364454717488153,105,1050,'Team 50',81,17,11,13.5802,4,4.93827,66,81.4815,-67.9012,0,0,0,0,0); DROP TABLE IF EXISTS `RawData2`; CREATE TABLE `RawData2` ( `id` int(11) NOT NULL DEFAULT '0', `customer_id` int(11) NOT NULL, `org_struct_id` int(11) NOT NULL, `org_struct_version_id` int(11) NOT NULL, `org_struct_name` varchar(100) NOT NULL, `org_struct_type_id` int(11) NOT NULL, `created_date_time` datetime DEFAULT NULL, `deleted_date_time` datetime DEFAULT NULL, `start_date_time` datetime NOT NULL, `end_date_time` datetime DEFAULT NULL, `lft` int(11) NOT NULL, `rgt` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `RawData2` VALUES (1,1,1,1,'root',1,'2012-04-02 10:04:26',NULL,'2012-04-02 10:04:26',NULL,1,124),(2,1,2,2,'UK',2,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,2,123),(3,1,11,11,'Region 1',3,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,3,26),(4,1,12,12,'Region 2',3,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,27,50),(5,1,13,13,'Region 3',3,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,51,74),(6,1,14,14,'Region 4',3,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,75,98),(7,1,15,15,'Region 5',3,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,99,122),(8,1,101,101,'Depot 1',4,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,4,25),(9,1,102,102,'Depot 2',4,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,28,49),(10,1,103,103,'Depot 3',4,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,52,73),(11,1,104,104,'Depot 4',4,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,76,97),(12,1,105,105,'Depot 5',4,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,100,121),(13,1,1001,1001,'Team 1',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,5,6),(14,1,1002,1002,'Team 2',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,7,8),(15,1,1003,1003,'Team 3',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,9,10),(16,1,1004,1004,'Team 4',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,11,12),(17,1,1005,1005,'Team 5',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,13,14),(18,1,1006,1006,'Team 6',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,15,16),(19,1,1007,1007,'Team 7',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,17,18),(20,1,1008,1008,'Team 8',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,19,20),(21,1,1009,1009,'Team 9',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,21,22),(22,1,1010,1010,'Team 10',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,23,24),(23,1,1011,1011,'Team 11',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,29,30),(24,1,1012,1012,'Team 12',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,31,32),(25,1,1013,1013,'Team 13',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,33,34),(26,1,1014,1014,'Team 14',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,35,36),(27,1,1015,1015,'Team 15',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,37,38),(28,1,1016,1016,'Team 16',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,39,40),(29,1,1017,1017,'Team 17',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,41,42),(30,1,1018,1018,'Team 18',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,43,44),(31,1,1019,1019,'Team 19',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,45,46),(32,1,1020,1020,'Team 20',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,47,48),(33,1,1021,1021,'Team 21',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,53,54),(34,1,1022,1022,'Team 22',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,55,56),(35,1,1023,1023,'Team 23',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,57,58),(36,1,1024,1024,'Team 24',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,59,60),(37,1,1025,1025,'Team 25',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,61,62),(38,1,1026,1026,'Team 26',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,63,64),(39,1,1027,1027,'Team 27',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,65,66),(40,1,1028,1028,'Team 28',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,67,68),(41,1,1029,1029,'Team 29',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,69,70),(42,1,1030,1030,'Team 30',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,71,72),(43,1,1031,1031,'Team 31',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,77,78),(44,1,1032,1032,'Team 32',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,79,80),(45,1,1033,1033,'Team 33',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,81,82),(46,1,1034,1034,'Team 34',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,83,84),(47,1,1035,1035,'Team 35',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,85,86),(48,1,1036,1036,'Team 36',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,87,88),(49,1,1037,1037,'Team 37',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,89,90),(50,1,1038,1038,'Team 38',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,91,92),(51,1,1039,1039,'Team 39',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,93,94),(52,1,1040,1040,'Team 40',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,95,96),(53,1,1041,1041,'Team 41',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,101,102),(54,1,1042,1042,'Team 42',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,103,104),(55,1,1043,1043,'Team 43',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,105,106),(56,1,1044,1044,'Team 44',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,107,108),(57,1,1045,1045,'Team 45',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,109,110),(58,1,1046,1046,'Team 46',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,111,112),(59,1,1047,1047,'Team 47',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,113,114),(60,1,1048,1048,'Team 48',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,115,116),(61,1,1049,1049,'Team 49',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,117,118),(62,1,1050,1050,'Team 50',7,'2012-04-02 10:04:27',NULL,'2011-01-01 00:00:00',NULL,119,120); DROP PROCEDURE IF EXISTS sp_test6; DROP PROCEDURE IF EXISTS sp_test7; DELIMITER $$; CREATE PROCEDURE `sp_test6`(IN sortcolumn VARCHAR(100)) BEGIN SELECT CASE WHEN sortcolumn='parent_org_struct_name' THEN os.org_struct_name ELSE 'NULL STRING' END AS sort_asc_string_1, d.nps_score AS sort_numeric FROM RawData d LEFT JOIN RawData2 os ON os.org_struct_id = d.parent_org_struct_id GROUP BY sort_asc_string_1,sort_numeric; END; $$ CREATE PROCEDURE `sp_test7`(IN sortcolumn VARCHAR(100)) BEGIN SELECT CASE WHEN sortcolumn='parent_org_struct_name' THEN os.org_struct_name ELSE 'NULL STRING' END AS sort_asc_string_1, d.nps_score AS sort_numeric FROM RawData d LEFT JOIN RawData2 os ON os.org_struct_id = d.parent_org_struct_id ORDER BY sort_asc_string_1,sort_numeric; END; $$ DELIMITER ;$$ --enable_query_log call sp_test7('nps_score'); truncate RawData; INSERT INTO `RawData` VALUES (22364454717493915,NULL,2,'UK',1737,445,306,17.6166,56,3.22395,1375,79.1595,-61.5429,0,0,0,0,0); call sp_test7('nps_score'); truncate RawData; INSERT INTO `RawData` VALUES (22364454717488109,101,1001,'Team 1',67,18,15,22.3881,2,2.98507,50,74.6269,-52.2388,0,0,0,0,0),(22364454717488110,101,1002,'Team 2',18,2,2,11.1111,0,0,16,88.8889,-77.7778,0,0,0,0,0),(22364454717488111,101,1003,'Team 3',50,14,6,12,1,2,43,86,-74,0,0,0,0,0),(22364454717488112,101,1004,'Team 4',21,9,2,9.52381,1,4.7619,18,85.7143,-76.1905,0,0,0,0,0),(22364454717488113,101,1005,'Team 5',15,6,0,0,3,20,12,80,-80,0,0,0,0,0),(22364454717488114,101,1007,'Team 7',49,13,5,10.2041,3,6.12245,41,83.6735,-73.4694,0,0,0,0,0),(22364454717488115,101,1009,'Team 9',48,9,13,27.0833,0,0,35,72.9167,-45.8333,0,0,0,0,0),(22364454717488116,101,1010,'Team 10',18,4,3,16.6667,1,5.55556,14,77.7778,-61.1111,0,0,0,0,0),(22364454717488117,102,1011,'Team 11',16,4,3,18.75,0,0,13,81.25,-62.5,0,0,0,0,0),(22364454717488118,102,1012,'Team 12',51,13,13,25.4902,0,0,38,74.5098,-49.0196,0,0,0,0,0),(22364454717488119,102,1013,'Team 13',66,22,13,19.697,3,4.54545,50,75.7576,-56.0606,0,0,0,0,0),(22364454717488120,102,1014,'Team 14',11,1,2,18.1818,1,9.09091,8,72.7273,-54.5455,0,0,0,0,0),(22364454717488121,102,1015,'Team 15',16,1,2,12.5,1,6.25,13,81.25,-68.75,0,0,0,0,0),(22364454717488122,102,1016,'Team 16',71,12,10,14.0845,0,0,61,85.9155,-71.831,0,0,0,0,0),(22364454717488123,102,1017,'Team 17',96,25,17,17.7083,3,3.125,76,79.1667,-61.4583,0,0,0,0,0),(22364454717488124,102,1018,'Team 18',53,15,5,9.43396,1,1.88679,47,88.6792,-79.2453,0,0,0,0,0),(22364454717488125,102,1019,'Team 19',87,17,17,19.5402,1,1.14943,69,79.3103,-59.7701,0,0,0,0,0),(22364454717488126,102,1020,'Team 20',29,8,3,10.3448,1,3.44828,25,86.2069,-75.8621,0,0,0,0,0),(22364454717488127,103,1021,'Team 21',40,13,6,15,2,5,32,80,-65,0,0,0,0,0),(22364454717488128,103,1022,'Team 22',32,7,3,9.375,2,6.25,27,84.375,-75,0,0,0,0,0),(22364454717488129,103,1023,'Team 23',20,6,4,20,0,0,16,80,-60,0,0,0,0,0),(22364454717488130,103,1024,'Team 24',19,6,2,10.5263,0,0,17,89.4737,-78.9474,0,0,0,0,0),(22364454717488131,103,1026,'Team 26',47,18,13,27.6596,1,2.12766,33,70.2128,-42.5532,0,0,0,0,0),(22364454717488132,103,1027,'Team 27',18,5,4,22.2222,1,5.55556,13,72.2222,-50,0,0,0,0,0),(22364454717488133,103,1028,'Team 28',49,15,10,20.4082,0,0,39,79.5918,-59.1837,0,0,0,0,0),(22364454717488134,103,1030,'Team 30',49,13,6,12.2449,1,2.04082,42,85.7143,-73.4694,0,0,0,0,0),(22364454717488135,104,1031,'Team 31',30,6,8,26.6667,0,0,22,73.3333,-46.6667,0,0,0,0,0),(22364454717488136,104,1032,'Team 32',39,7,9,23.0769,1,2.5641,29,74.359,-51.2821,0,0,0,0,0),(22364454717488137,104,1033,'Team 33',32,14,4,12.5,2,6.25,26,81.25,-68.75,0,0,0,0,0),(22364454717488138,104,1034,'Team 34',15,5,3,20,0,0,12,80,-60,0,0,0,0,0),(22364454717488139,104,1035,'Team 35',19,3,1,5.26316,1,5.26316,17,89.4737,-84.2105,0,0,0,0,0),(22364454717488140,104,1036,'Team 36',31,6,6,19.3548,1,3.22581,24,77.4194,-58.0645,0,0,0,0,0),(22364454717488141,104,1037,'Team 37',31,4,3,9.67742,1,3.22581,27,87.0968,-77.4194,0,0,0,0,0),(22364454717488142,104,1038,'Team 38',13,2,3,23.0769,0,0,10,76.9231,-53.8462,0,0,0,0,0),(22364454717488143,104,1039,'Team 39',18,5,2,11.1111,0,0,16,88.8889,-77.7778,0,0,0,0,0),(22364454717488144,104,1040,'Team 40',53,15,9,16.9811,3,5.66038,41,77.3585,-60.3774,0,0,0,0,0),(22364454717488145,105,1041,'Team 41',30,10,7,23.3333,0,0,23,76.6667,-53.3333,0,0,0,0,0),(22364454717488146,105,1042,'Team 42',40,9,8,20,0,0,32,80,-60,0,0,0,0,0),(22364454717488147,105,1043,'Team 43',61,20,12,19.6721,7,11.4754,42,68.8525,-49.1803,0,0,0,0,0),(22364454717488148,105,1044,'Team 44',37,11,5,13.5135,0,0,32,86.4865,-72.973,0,0,0,0,0),(22364454717488149,105,1045,'Team 45',51,11,12,23.5294,4,7.84314,35,68.6274,-45.098,0,0,0,0,0),(22364454717488150,105,1047,'Team 47',26,8,7,26.9231,2,7.69231,17,65.3846,-38.4615,0,0,0,0,0),(22364454717488151,105,1048,'Team 48',32,6,9,28.125,1,3.125,22,68.75,-40.625,0,0,0,0,0),(22364454717488152,105,1049,'Team 49',42,10,8,19.0476,0,0,34,80.9524,-61.9048,0,0,0,0,0),(22364454717488153,105,1050,'Team 50',81,17,11,13.5802,4,4.93827,66,81.4815,-67.9012,0,0,0,0,0); call sp_test7('nps_score');