5 Replies Latest reply: Dec 20, 2016 2:07 AM by Shraddha Gajare RSS

    Number to names

    Abu Sayeed Baseer

      Hello All

       

      Need a query to change MonthNumbers to Month Names. The table columns are:

       

      mnthly_sales:

      LOAD BRANCH_CD,

        YYMM,

          left(YYMM,4) as year,

          Right(YYMM,2) as Month,

           [CUST#],

          BRANCH_CD&'-'&[SALESMAN#] AS CUSTSALES#,

          [VNDR#]&'-'&[ITEM#] AS ITMVND#,

          [VNDR#],

        CUST_TYPE_CD,

        NET_SALES_VALUE;

       

       

      MNTHLY_SALES_ACTVTY:

      SELECT "BRANCH_CD",

        "YYMM",

          "CUST#",

        "SALESMAN#",

        "VNDR#",

        "ITEM#",

        "CUST_TYPE_CD",

        "NET_SALES_VALUE"

      FROM "MBTW"."MNTHLY_SALES_ACTVTY" WHERE YYMM BETWEEN 201201 AND 201612 ;

       

       

      Output: for the year and month is as shown

       

      1.png 2.png

      The requirement is to change the Months format from Numbers to Names (Jan, Feb , Mar......)

      for this i tried IF statement

      LOAD BRANCH_CD,

        YYMM,

          left(YYMM,4) as year,

          Right(YYMM,2) as Month,

        if(month(YYMM)='1','Jan',

        if(month(YYMM)='2','Feb',

        if(month(YYMM)='3','Mar',

        if(month(YYMM)='4','Apr',

        if(month(YYMM)='5','May',

        if(month(YYMM)='6','Jun',

        if(month(YYMM)='7','Jul',

        if(month(YYMM)='8','Aug',

        if(month(YYMM)='9','Sep',

        if(month(YYMM)='10','Oct',

        if(month(YYMM)='11','Nov',

        if(month(YYMM)='12','Dec')))))))))))) as MonthName,

      and got this Output but the issue is some months are not displaying.

      3.png

      how to fix this ????????????????????????????