5 Replies Latest reply: Jan 27, 2014 9:04 AM by Srikanth P RSS

    Putting months in chronological order using script

    Sara Titmuss

      Hi,

       

      I am trying to order my months table in chronological order instead of alphabetical.  The script I have is the following:

       

      VMO_NEW_OPPORTUNITY:

      SQL
      select
      type1.CODEDESC Type,
      OPT9.CODEDESC as Division,
      VMO_OPPORTUNITY.IDUSER TM,
      count(VMO_OPPORTUNITY.IDUSER) Opportunity_Count,
      to_char(VMO_OPPORTUNITY.created,'MON' ) as Month_created,
      to_char(VMO_OPPORTUNITY.created,'YYYY' ) as YEAR_created
      from ifsapp.vmo_opportunity
      left join IFSAPP.VMO_BASE_CODEDESC opt9 on VMO_OPPORTUNITY.OKM_DIVISION=opt9.IDCOD
      left join IFSAPP.VMO_BASE_CODEDESC type1 on VMO_OPPORTUNITY.KindofOpp =type1.IDCOD
      where idcodprs ='prs.001'
      and (OPT9.idlng = 'en' or OPT9.idlng is null)
      and (type1.idlng = 'en' or type1.idlng is null)
      group by
      type1.CODEDESC,
      to_char(VMO_OPPORTUNITY.created,'MON' ) ,
      to_char(VMO_OPPORTUNITY.created,'YYYY' ),
      OPT9.CODEDESC ,
      VMO_OPPORTUNITY.IDUSER
      ;

       

      I know I need to load the months as JAN = 1 etc, but can't remember the SQL for it.  Can someone jog my memory please?