List the indexes in SQL Server

Execute the below query to list th enumber of indexes used in your Sql server:
SELECT * FROM sys.dm_db_missing_index_details
--Where Database_ID = .....

By executing the above query you can find the number of columns used in the index.

Change Background color in SSRS - Switch Syntax

Switch Syntax to change color in SSRS report

=Switch(Fields!Performance.Value >= 80, "Green", Fields!Performance.Value >= 60, "Amber", Fields!Performance.Value < 60, "Red")

Creating Parameters in Pentaho Dashboard using SQL Query

We can parse SQl query in an Xaction file to create a parameter in Pentaho dashboard.
1. Create an xaction file like below:

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
  <name>ListOperator.xaction</name>
  <title>%title</title>
  <version>1</version>
  <logging-level>debug</logging-level>
  <documentation>
    <author>Mahadevan</author> 
    <description>%description</description> 
    <help/> 
    <result-type>rule</result-type> 
    <icon>SQL_Datasource.png</icon>
  </documentation>

  <inputs>
      <FROM type="string">
      <sources>
        <request>FROM</request>
      </sources>
    </FROM>
    <TO type="string">
      <sources>
        <request>TO</request>
      </sources>
    </TO>
 </inputs>

   

  <outputs>
    <rule-result type="result-set"/>
  </outputs>

  <resources/>
 
  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Query For Operator Info</action-type>
     <action-inputs>
         <FROM type="string"/>
        <TO type="string"/>
       </action-inputs>  
      <action-outputs>
        <query-result type="result-set" mapping="rule-result"/>
      </action-outputs>
      <component-definition>
        <jndi>Telecount</jndi> 
        <query><![CDATA[select count(smskey) AS total, Operator AS Operators from vw_telecount
                            WHERE  Createddate >= ('{FROM}') AND Createddate <=('{TO}') 
                            GROUP BY Operator]]></query>
      </component-definition>
    </action-definition>

  </actions>
</action-sequence>

Step 2: Create an object for parameter in dashboard template as shown below:
ListOperators =
{
  name: "ListOperators",
  type: "selectMulti",
  solution: "PentahoDemo",
  path: "Dashboards/FilterA/List",
  action: "ListOperators.xaction",
  listeners:["startDate","endDate"],
  parameters: [["FROM","startDate"],["TO","endDate"]],
  parameter:"operators",
  htmlObject: "ListOperators_Object",
  size: "4",
  executeAtStart: true,
  preExecution:function(){},
  postExecution:function(){}
}

3. Call the object in your Dashbord template.

<td valign = "top"    style="width: 5%; text-align: left;"><span
                        style="font-family: Arial;">Operators:</span><br>
                </td>
                <td  valign = "top" style="width: 20%; text-align: left;">
                <div valign = "top"  id="ListOperators_Object"></div>

4. Update repository and open your dashboard, you can see the parameter.

Thanks
Mahadevan

Prerequesites for Pentaho

 Pre-requesites for Pentaho Dashboard and Flash Charts

1. Before installing Pentaho in your system, install Java Run Time Environment 6 or higher version.
2. Install Flash Player to view flash charts.
3. If you are developing some open flash charts in your dashboard and charts then download open-flash-chart-full-embedded-font.swf  file and place it in the following location "\BI Server (biserver-ce-3.6.0-stable)\biserver-ce\pentaho-solutions\system\pentaho-cdf\".
4. For better view use Mozilla Firefox browser.

Regards
Mahadevan

To change the Default Port Number of Pentaho Server

To change the Default Port Number of Pentaho Server, navigate to web.xml file in the following path:
\BI Server
(biserver-ce-3.6.0-stable)\biserver-ce\tomcat\webapps\pentaho\WEB-INF


Open Web.xml file and  change the port number in highlighted text, providing IP address will allow user to share the pentaho works with other machine in same domain:

<context-param>
        <param-name>base-url</param-name>
        <param-value>http://192.168.60.195:8080/pentaho/</param-value>
    </context-param>

Mail Sending in SQL server

1.     Mail Send from SQL

USE [Timesheet09-Oct-2009]

GO

/****** Object:  StoredProcedure [dbo].[pcWarantyStartDatePreAlert]    Script Date: 08/04/2010 09:21:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[pcWarantyStartDatePreAlert]

--    @PONumber VARCHAR(100),

--    @TimeIntervalId INT

AS

BEGIN

      BEGIN TRY

                  IF EXISTS( SELECT 0 FROM [Timesheet09-Oct-2009].[dbo].[Projects] WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

                  BEGIN

                        EXEC msdb.dbo.sp_send_dbmail

                        @profile_name = 'WarrantyStartDateAlerts',

                        @recipients = 'mahadevan.v@congruentindia.com;',

                        @copy_recipients = 'SET NOCOUNT ON

                                                      SELECT EmailAddress From [User]

                                                      JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects

                                                      WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

                                                      ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID

                                                      SET NOCOUNT OFF',

                        @query = 'SET NOCOUNT ON

                                      SELECT [ProjectName]

                                      ,[RegionMaster].[RegionName]

                                      ,[Status]

                                      ,[User].Firstname

                                      ,[WarrantyStartDate]

                                      ,[WarrantyDays]

                                      ,[WarrantyEndDate]

                                FROM [Timesheet09-Oct-2009].[dbo].[Projects]

                                LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID

                                LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID

                              WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)

                         SET NOCOUNT OFF',

                        @subject = 'The Warranty date for the project listed starts from tomorrow'

                  END

      END TRY

      BEGIN CATCH

            DECLARE @Error VARCHAR(1000)

            SET @Error = ERROR_MESSAGE()

            RAISERROR(@Error, 16, 1)

      END CATCH

END

Mail Send from SQL Format 2

USE [Timesheet09-Oct-2009]

GO

/****** Object:  StoredProcedure [dbo].[pcWarantyStartDateAlert]    Script Date: 08/04/2010 10:58:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[pcWarantyStartDateAlert]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML NVARCHAR(MAX)

DECLARE @LeadMailID NVARCHAR(MAX)

DECLARE Mail_Cursor CURSOR FOR

SELECT EmailAddress From [User]

JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects

WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))

ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID

OPEN Mail_Cursor

FETCH NEXT FROM Mail_Cursor

INTO @LeadMailID

WHILE @@FETCH_STATUS = 0

      BEGIN

            SET @LeadMailID = REPLACE(@LeadMailID,',',';')

FETCH NEXT FROM Mail_Cursor

            INTO @LeadMailID

      END

CLOSE Mail_Cursor

DEALLOCATE Mail_Cursor

SET @tableHTML =

    N'<H1>Project Waranty Start Date Alert</H1>' +

    N'<table border="1">' +

    N'<tr><th>SNo</th><th>Projecdt Name</th><th>Region Name</th>' +

    N'<th>Status</th><th>Responsible Person</th><th>Waranty Start Date</th>' +

    N'<th>Warranty Days</th><th>Warranty End Date</th></tr>' +

    CAST ( ( SELECT    td = ROW_NUMBER() OVER (ORDER BY [ProjectName]), ''

                                ,td = [ProjectName], ''

                                ,td = [RegionMaster].[RegionName],''

                                ,td =[Status], ''

                                ,td =[User].Firstname, ''

                                ,td =[WarrantyStartDate], ''

                                ,td =[WarrantyDays], ''

                                ,td =[WarrantyEndDate], ''

                                FROM [Timesheet09-Oct-2009].[dbo].[Projects]

                                LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID

                                LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID

                              WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

       @subject = 'The Warranty date for the project listed starts Today',

       @profile_name = 'WarrantyStartDateAlert2',

       @recipients = @LeadMailID,

       @copy_recipients = 'mahadevan.v@congruentindia.com',

     @body = @tableHTML,

     @body_format = 'HTML' ;

SET NOCOUNT OFF;

END

<!--

USE [BIServer]

GO

/****** Object:  StoredProcedure [dbo].[sp_BIServerHealthCheckMail]    Script Date: 11/25/2010 15:01:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[sp_BIServerHealthCheckMail]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @tableHTML NVARCHAR(MAX)

DECLARE @PingCount INT

SELECT @Pingcount = Count(*) from PingServer

SET @tableHTML =

    N'<H1>CIT Server Health Check</H1>' +

    N'<table border="1">' +

    N'<tr><th>SNo</th><th>SYSTEM NAME</th>' +

    N'<th>PING RESULT</th>' +

    N'<th>SERVER STATUS</th>' +

    N'<th>PROCESSED DATE</th></tr>' +

    CAST ( ( SELECT    td = ROW_NUMBER() OVER (ORDER BY Ping DESC, PingServer.[CITServer]), ''

                                ,td = PingServer.[CITServer], ''

                                ,td = PING,''

                                ,td = CASE WHEN PING like '%Request timed out%' THEN 'Connection Failed'

                                          ELSE 'Connection Succeeded' END, ''

                                ,td =GETDATE(), ''

                                FROM [BIServer].[dbo].[PingServer]

                                JOIN (SELECT [CITServer],MAX(SERID) AS SIDS

                                          FROM [BIServer].[dbo].[PingServer]

                                          GROUP BY [CITServer]

                                          ) SERPING ON PingServer.SERID = SERPING.SIDS

                   ORDER BY PING DESC, PingServer.[CITServer]

                  FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

IF (@PingCount >0)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

       @subject = 'CIT SERVER HEALTH CHECK',

       @profile_name = 'BIServerHealthCheck',

       @recipients = 'sathishkumarg@congruentindia.com;Chalapathi.M@congruentindia.com;Satheeshkumar.m@congruentindia.com',

       @copy_recipients = 'vinod.s@congruentindia.com;mahadevan.v@congruentindia.com',

     @body = @tableHTML,

     @body_format = 'HTML' ;

END

SET NOCOUNT OFF;

END

SQL Query to find relationshib between tables in a database

The below query lists the relationship between tables based on primarykey and foriegnkey relationship:

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id

Regards
Mahadevan

Query to find the Scheduled Job status in SQL Server

The below query displays the history of Job processed in the sql server for last 5 days. it display the job status failed/passed with processed date and jobname:


SELECT    CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11))    AS RunDate
        ,job_id
        ,step_name
        ,step_id
        ,[message]
        ,CASE WHEN run_status =1 Then 'Passed'ELSE'Failed' END AS JobStatus
        ,run_duration
        ,[server]
FROM    msdb..sysjobhistory
Where Run_date >= CONVERT(char(8), (select dateadd (day,(-5), getdate())), 112)
AND step_id != 0
ORDER BY run_status,run_date

Regards
Mahadevan

Important Date Formats in SQL

SELECT substring(convert(char(20), Getdate(), 100), 1, 12)

Result:  Nov 25 2010

SELECT substring(convert(char(20), GETDATE(), 100), 1, 3)
+ ' ' + convert(char(4), year(GETDATE()))
Result: Nov 2010

SQL query to display Fiscal Month and Year

SELECT CASE WHEN MONTH(GETDATE()) >= 4 THEN MONTH(GETDATE()) - 3 ELSE MONTH(GETDATE()) + 9 END  AS FiscalMonth  
,CASE WHEN MONTH(GETDATE()) >= 4 THEN YEAR(GETDATE()) ELSE YEAR(GETDATE()) - 1 END AS FiscalYear

Result display Fiscal Month and Year

SQL Query to find row Count of Tables in a Database

Row Count of Tables in DB:

SELECT    Row_Number() Over (Order by tbl.name) As SNo
        ,[TableName]=tbl.name
        ,[RowCount] = SUM(CASE WHEN (pt.index_id < 2)
        AND (au.type = 1) THEN pt.rows ELSE 0 END)
FROM sys.tables tbl
INNER JOIN sys.partitions pt ON tbl.object_id = pt.object_id
INNER JOIN sys.allocation_units au ON pt.partition_id = au.container_id
GROUP BY tbl.name

Total Row Count in DB, useful for ETL Testing:

Select    Sum(TEMPTABLE.[RowCount]) As [Total] from sys.Tables tb2
LEFT jOIN (SELECT    Row_Number() Over (Order by tbl.name) As SNo
        ,[TableName]=tbl.name
        ,[RowCount] = SUM(CASE WHEN (pt.index_id < 2)
        AND (au.type = 1) THEN pt.rows ELSE 0 END)
FROM sys.tables tbl
INNER JOIN sys.partitions pt ON tbl.object_id = pt.object_id
INNER JOIN sys.allocation_units au ON pt.partition_id = au.container_id
GROUP BY tbl.name) TEMPTABLE ON TEMPTABLE.Tablename = tb2.name

Thanks
Mahadevan

Query to add Multi List Parameter in SSRS 2005 Reporting Server

 Step 1: Add a Table-valued function 'charlist_to_table' in your db.

 Check for function charlist_to_table or create one using below Stored Proc:

USE [Timesheet]
GO
/****** Object:  UserDefinedFunction [dbo].[charlist_to_table]    Script Date: 11/24/2010 18:47:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[charlist_to_table]
    (@list ntext,
    @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

            str varchar(4000),

            nstr nvarchar(2000)) AS

            BEGIN

            DECLARE @pos int,

            @textpos int,

            @chunklen smallint,

            @tmpstr nvarchar(4000),

            @leftover nvarchar(4000),

            @tmpval nvarchar(4000)

            SET @textpos = 1

            SET @leftover = ''

            WHILE @textpos <= datalength(@list) / 2

            BEGIN

            SET @chunklen = 4000 - datalength(@leftover) / 2

            SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

            SET @textpos = @textpos + @chunklen

            SET @pos = charindex(@delimiter, @tmpstr)

            WHILE @pos > 0

            BEGIN

            SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

            SET @pos = charindex(@delimiter, @tmpstr)

            END

            SET @leftover = @tmpstr

            END

            INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),

            ltrim(rtrim(@leftover)))

RETURN

END

Step 2: Call that function in your Stored Proc as shown in sample procedure below:

USE [Timesheet]
GO
/****** Object:  StoredProcedure [dbo].[sp_CalculateProjectRevenue]    Script Date: 11/24/2010 18:28:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:       
-- Create date:
-- Description:   
-- =============================================
ALTER PROCEDURE [dbo].[sp_CalculateProjectRevenue]
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL,
    @ProjectID VARCHAR(2000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements. --165118
    SET NOCOUNT ON;
BEGIN

  SELECT    Main.TrackID
            ,Main.ProjectName
            ,Main.ProjectID
            ,Main.EstimationNo
            ,Main.EstRevenue
            ,Main.TotalHours
            ,Projects.IsBillable AS ProjBill
            ,Main.RegionID
            ,Main.Firstname
            ,Main.ResourceID
            ,CONVERT(VARCHAR(10),Main.Datee,101) AS DATED
            --,Main.IsBillable
            ,Tasks.TaskName
            ,Timetrack.Description
            ,CASE Main.IsBillable WHEN 1 THEN Main.Duration ELSE 0 END AS BillHrs
            ,CASE Main.IsBillable WHEN 0 THEN Main.Duration ELSE 0 END AS NonBillHrs
            ,ISNULL(Main.HourlyRate,0) AS HourlyRate
            ,ISNULL(Main.OffshoreRate,0) AS OffshoreRate
            ,ISNULL(Main.OnsiteRate,0) AS OnsiteRate
           
FROM Main
WHERE Main.Datee >= @FromDate AND Main.Datee <= @ToDate
        AND Main.ProjectID  IN (SELECT CAST([str]AS INT) FROM charlist_to_table(@ProjectID,','))

END
SET NOCOUNT OFF;
END







Regards

Mahadevan

MY SQL Stored Procedure to Load Random Data to Table

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