Results 1 to 7 of 7

Thread: Missing Values

  1. #1
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47

    Unanswered: Missing Values

    Hello people,

    I have been writing some queries and reports using SQL (with Crystal and ASP.NET), and have a problem. Something I often need to do is find the number of records for each month, so there is a date field and a currency field, and I need to sum all the currency values for each month.

    Firstly, to get a group for each month of each year I am using ....GROUP BY Month(DateField), Year(DateField)
    is this correct or is there a better way?

    The more problematic thing is, where there are no records for certain month, of course this month does not show up in the query results. This is a problem when displaying results in a table/chart. Is there any way to force each month to appear (with a 0 in the SUMed field)?

    Any advice would be most, most welcome,

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could define a table say tblmonth and define a join, which will allways retrieve the a month even if there are no entries for that month

  3. #3
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    I'll have a go at that, thanks

    any idea what join that would be?

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    I think it would be a left join (Outer joins allow select all from this side and show any matching on the other).

    Give it a go and if what you get is silly switch to right join as there is a small chance I got it wrong.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll need a table of months, or use an integers table to generate them

    see Selecting all months even if they're missing (site registration may be required, but it's free)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    Thanks for your help on this, I seem to have it working now.

    I have a similar problem with a report I am writing in crystal reports. I am trying to create a group calendar from Outlook using a crosstab, but not all dates show up (if noone has an appointment booked on that day).

    What I need is to do is get a datasource with a list of days which I can do a join on to force all dates to appear. The problem is that the dates from Outlook are just dates but other applications such as access, excel use date/time fields and so the join does not work! Does anyone know where I can get a data source of just dates to use in the join?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a data source of just dates? yeah, you can generate it from an integers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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