Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Location
    India
    Posts
    5

    Unanswered: Displaying default data

    When i run the query given below i can see the following data.

    month year val

    January 2008 5
    June 2008 10

    But what i want is that, if the month is empty then it should show 0

    e.g.
    month year val

    January 2008 5
    February 2008 0
    March 2008 0
    June 2008 10

    and so on, ie if the month is empty it should show zero for that month in val column. Is this possible or do i have to do it at the front end side.


    Query

    select monthname(timestamp) as month , year(timestamp) as year, count(timestamp) as val

    from scm_change where timestamp > '2008-01-01' and timestamp < DATEADD( day, 365, '2008-01-01' )

    group by month(timestamp) , monthname(timestamp), year(timestamp)

    order by year(timestamp), month(timestamp)

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can do if you have a numbers table
    Code:
    select 2008 year, mth, count(timestamp) val
    from 
    (SELECT number mth
      FROM master..spt_values
      WHERE type = 'P'
        AND number between 1 and 12) months
    left join scm_change 
      on mth=month(timestamp) 
      and timestamp between '20080101' and '20081231'
    group by mth
    order by mth
    Last edited by pdreyer; 08-07-07 at 10:41.

  3. #3
    Join Date
    May 2006
    Location
    India
    Posts
    5
    Thx pdreyer but i did it in java.

    Cannot create any new tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •