#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    Outer join on month/year


    I have a query like
    Code:
    select datepart(yyyy, mydate), datepart (mm, mydate), sum(myvalue)
    that returns something like this:

    2011 1 5
    2011 2 8
    2011 3 4
    2011 5 7

    There are no records in the table for April (4), as you can see. Normally I would do an outer join on a table to make sure that I return a record like "2011 4 0" but I'm not sure how to accomplish this when there is no real table to join against. I don't want to use a temp table because I'd like it to be a "self-contained" query.

    Any ideas?

    Thanks!
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by mateoc15
    I don't want to use a temp table because I'd like it to be a "self-contained" query.
    you really should consider using a numbers table

    seriously, you won't regret it

    which range of years did you want to see?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo