monthwise data query

• nayanamoolya

  SSC Journeyman

  Points: 76

  More actions

  January 2, 2008 at 1:05 am

  #70561

  Hi,

  I have a table consisting of

  name, ymonth, yyear, value

  I want for a particular name data be displayed month and year wise

  So query was

  Select ymonth,yyear, sum(value)

  from xvgt where name='ABC'

  I am getting the output but the ymonth column is sorted alphabetically not monthwise.

  This the issue. Pls help is required.

  Regards

  Nayana

• PDreyer

  SSC Journeyman

  Points: 86

  More actions

  January 2, 2008 at 3:56 am

  #763961

  select ymonth from(

  select

  'Jan' union all select

  'Feb' union all select

  'Dec')f(ymonth)

  order by convert(datetime,'1 '+ymonth+' 1900')

  -- OR --

  select ymonth from(

  select

  '1' union all select

  '2' union all select

  '12')f(ymonth)

  order by convert(int,ymonth)

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  January 3, 2008 at 12:18 am

  #764275

  This is why it's such a bad idea to ever store date information in any type of Data-Type other than DATETIME...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)