First execute the below sp to create sample table:
DROP TABLE IF EXISTS `telecount`.`sms_detail`;
CREATE TABLE `telecount`.`sms_detail` (
`SMSKEY` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sumID` int(10) NOT NULL,
`StatusFlag` varchar(45) DEFAULT NULL,
`DC` varchar(45) DEFAULT NULL,
`Product` varchar(45) DEFAULT NULL,
`TNAME` varchar(45) DEFAULT NULL,
`MSGTYPE` varchar(45) DEFAULT NULL,
`COUNT` decimal(10,0) DEFAULT NULL,
`SENDDATE` datetime DEFAULT NULL,
`Operator` varchar(45) DEFAULT NULL,
`DELDATE` datetime DEFAULT NULL,
`Duration` time DEFAULT NULL,
PRIMARY KEY (`SMSKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Then create and execute the below stored procs:
DELIMITER $$
DROP PROCEDURE IF EXISTS `SampleProc` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SampleProc`()
BEGIN
DECLARE x INT;
SET x = 1;
WHILE x <= 100 DO
INSERT INTO sms_Detail
(sumID
,StatusFlag
,DC
,Product
,TNAME
,MSGTYPE
,COUNT
,SENDDATE
,OPERATOR
,DELDATE
,DURATION)
VALUES (x
, CASE WHEN x MOD 10 < 3 THEN 'FAILURE'
WHEN x MOD 10 = 4 THEN 'REJECTED'
ELSE 'SUCCESS' END
, CASE WHEN x MOD 11 + x MOD 9 =1 THEN 'VSNL'
WHEN x MOD 11 + x MOD 9 =2 THEN 'SIFY'
WHEN x MOD 11 + x MOD 9 =3 THEN 'TELENET'
WHEN x MOD 11 + x MOD 9 =4 THEN 'GREAMSRD'
WHEN x MOD 11 + x MOD 9 =5 THEN 'AIRTEL'
WHEN x MOD 11 + x MOD 9 =6 THEN 'AIRONE'
WHEN x MOD 11 + x MOD 9 =7 THEN 'BSNL'
WHEN x MOD 11 + x MOD 9 =8 THEN 'VSNL'
WHEN x MOD 11 + x MOD 9 =9 THEN 'SIFY'
WHEN x MOD 11 + x MOD 9 =10 THEN 'SIFY'
WHEN x MOD 11 + x MOD 9 =11 THEN 'TELENET'
WHEN x MOD 11 + x MOD 9 =12 THEN 'VSNL'
WHEN x MOD 11 + x MOD 9 =13 THEN 'VSNL'
WHEN x MOD 11 + x MOD 9 =14 THEN 'VSNL'
ELSE 'BSNL' END
, CASE WHEN x MOD 10 + x MOD 8 =1 THEN 'SMPPBOX'
WHEN x MOD 10 + x MOD 8 =2 THEN 'GEMPAC'
WHEN x MOD 10 + x MOD 8 =3 THEN 'BB-II'
WHEN x MOD 10 + x MOD 8 =4 THEN 'BULK'
WHEN x MOD 10 + x MOD 8 =5 THEN 'ALPHA'
WHEN x MOD 10 + x MOD 8 =6 THEN 'SERVON'
WHEN x MOD 10 + x MOD 8 =7 THEN 'JB-22'
WHEN x MOD 10 + x MOD 8 =8 THEN 'NG'
WHEN x MOD 10 + x MOD 8 =9 THEN 'JAS'
WHEN x MOD 10 + x MOD 8 =10 THEN 'AIRTXT'
WHEN x MOD 10 + x MOD 8 =11 THEN 'NG'
WHEN x MOD 10 + x MOD 8 =12 THEN 'AIRTXT'
WHEN x MOD 10 + x MOD 8 =13 THEN 'AIRTXT'
WHEN x MOD 10 + x MOD 8 =14 THEN 'NG'
WHEN x MOD 10 + x MOD 8 =15 THEN 'BULK'
WHEN x MOD 10 + x MOD 8 =16 THEN 'NG'
ELSE 'SMPPBOX' END
, CASE WHEN x MOD 11 + x MOD 15 =1 THEN 'MBL_DISH'
WHEN x MOD 11 + x MOD 15 =2 THEN 'SMPP_OTHERS'
WHEN x MOD 11 + x MOD 15 =3 THEN 'MBL_CENTNI'
WHEN x MOD 11 + x MOD 15 =4 THEN 'MBL_CITI'
WHEN x MOD 11 + x MOD 15 =5 THEN 'EBL_HDFC'
WHEN x MOD 11 + x MOD 15 =6 THEN 'MBL_CITI'
WHEN x MOD 11 + x MOD 15 =7 THEN 'EBL_HDFC'
WHEN x MOD 11 + x MOD 15 =8 THEN 'MBL_ICICI'
WHEN x MOD 11 + x MOD 15 =9 THEN 'MBL_CENTNI'
WHEN x MOD 11 + x MOD 15 =10 THEN 'MBL_ICICI'
WHEN x MOD 11 + x MOD 15 =11 THEN 'MBL_AXIS'
WHEN x MOD 11 + x MOD 15 =12 THEN 'SMPP_DEFAULT'
WHEN x MOD 11 + x MOD 15 =13 THEN 'SMPP_CENTN'
WHEN x MOD 11 + x MOD 15 =14 THEN 'MBL_HDFC'
WHEN x MOD 11 + x MOD 15 =15 THEN 'MBL_OTHERS'
WHEN x MOD 11 + x MOD 15 =16 THEN 'MBL_OTHERS'
WHEN x MOD 11 + x MOD 15 =17 THEN 'MBL_CENTN'
WHEN x MOD 11 + x MOD 15 =18 THEN 'EBL_OTHERS'
WHEN x MOD 11 + x MOD 15 =19 THEN 'MBL_CUB'
ELSE 'MBL_HDFC' END
, CASE WHEN x MOD 8 + x MOD 7 =1 THEN 'AM'
WHEN x MOD 8 + x MOD 7 =2 THEN 'EV'
WHEN x MOD 8 + x MOD 7 =3 THEN 'DY'
WHEN x MOD 8 + x MOD 7 =4 THEN 'NT'
WHEN x MOD 8 + x MOD 7 =5 THEN 'NN'
WHEN x MOD 8 + x MOD 7 =6 THEN 'MD'
WHEN x MOD 8 + x MOD 7 =7 THEN 'EV'
WHEN x MOD 8 + x MOD 7 =8 THEN 'PM'
WHEN x MOD 8 + x MOD 7 =9 THEN 'AM'
WHEN x MOD 8 + x MOD 7 =10 THEN 'AM'
WHEN x MOD 8 + x MOD 7 =11 THEN 'DY'
ELSE 'PM' END
, 1
, Now()
, CASE WHEN x MOD 5 + x MOD 6 =1 THEN 'AIRWEB'
WHEN x MOD 5 + x MOD 6 =2 THEN 'WEBAIR'
WHEN x MOD 5 + x MOD 6 =3 THEN 'TELESTAR'
WHEN x MOD 5 + x MOD 6 =4 THEN 'CITWEB'
WHEN x MOD 5 + x MOD 6 =5 THEN 'WEBWING'
WHEN x MOD 5 + x MOD 6 =6 THEN 'AIRWEB'
WHEN x MOD 5 + x MOD 6 =7 THEN 'AIRWEB'
WHEN x MOD 5 + x MOD 6 =8 THEN 'CITWEB'
WHEN x MOD 5 + x MOD 6 =9 THEN 'TELESTAR'
ELSE 'AIRWEB' END
, CASE WHEN x MOD 9 + x MOD 18 =1 THEN NOW() + INTERVAL 1 MINUTE
WHEN x MOD 9 + x MOD 18 =2 THEN NOW() + INTERVAL 2 MINUTE
WHEN x MOD 9 + x MOD 18 =3 THEN NOW() + INTERVAL 3 MINUTE
WHEN x MOD 9 + x MOD 18 =4 THEN NOW() + INTERVAL 4 MINUTE
WHEN x MOD 9 + x MOD 18 =5 THEN NOW() + INTERVAL 5 MINUTE
WHEN x MOD 9 + x MOD 18 =6 THEN NOW() + INTERVAL 6 MINUTE
WHEN x MOD 9 + x MOD 18 =7 THEN NOW() + INTERVAL 7 MINUTE
WHEN x MOD 9 + x MOD 18 =8 THEN NOW() + INTERVAL 8 MINUTE
WHEN x MOD 9 + x MOD 18 =9 THEN NOW() + INTERVAL 10 MINUTE
WHEN x MOD 9 + x MOD 18 =10 THEN NOW() + INTERVAL 11 MINUTE
WHEN x MOD 9 + x MOD 18 =11 THEN NOW() + INTERVAL 13 MINUTE
WHEN x MOD 9 + x MOD 18 =12 THEN NOW() + INTERVAL 16 MINUTE
WHEN x MOD 9 + x MOD 18 =13 THEN NOW() + INTERVAL 18 MINUTE
WHEN x MOD 9 + x MOD 18 =14 THEN NOW() + INTERVAL 19 MINUTE
WHEN x MOD 9 + x MOD 18 =15 THEN NOW() + INTERVAL 7 MINUTE
WHEN x MOD 9 + x MOD 18 =16 THEN NOW() + INTERVAL 6 MINUTE
WHEN x MOD 9 + x MOD 18 =17 THEN NOW() + INTERVAL 9 MINUTE
ELSE NOW() + INTERVAL 3 MINUTE END
, '00:02:00');
SET x = x + 1;
END WHILE;
END $$
DELIMITER ;
Regards
Mahadevan
No comments:
Post a Comment