SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Return Value OR NULL

    I have a query that returns a score for each month per company location. The months are specified per a given time span. The trouble is, not every company location will have data for every month within the time span.

    Is there a way to modify the below query (perhaps using a correlated sub query) to return the score per location/month, or NULL if the location has no recorded monthly value?

    Code:
    select s.month, s.year, l.name, s.score
       from scores s
       inner join locations l on l.id = s.location_id and l.company_id = s.company_id
       where s.company_id = 22  
       and s.year*100+m between 201203 and 201207
       group by s.location_id, s.month, s.year
       order by s.year, s.month, l.name

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you'll need to have a month table as the left table in a LEFT OUTER JOIN

    it's a little trickier to make this a general purpose solution because you've separated the year and month into two different columns

    so, we'll use a generated year table along with a generated month table
    Code:
    SELECT y.yr
         , m.mth
         , data.name
         , data.score
      FROM ( SELECT 2012 AS yr
             UNION ALL SELECT 2013 ) AS yrs
    CROSS 
      JOIN ( SELECT 1 AS mth
             UNION ALL SELECT 2         
             UNION ALL SELECT 3         
             UNION ALL SELECT 4         
             UNION ALL SELECT 5         
             UNION ALL SELECT 6         
             UNION ALL SELECT 7         
             UNION ALL SELECT 8         
             UNION ALL SELECT 9         
             UNION ALL SELECT 10         
             UNION ALL SELECT 11         
             UNION ALL SELECT 12 ) AS mths           
    LEFT OUTER
      JOIN ( SELECT s.month
                  , s.year
                  , l.name
                  , s.score
               FROM scores s
             INNER 
               JOIN locations l 
                 ON l.id = s.location_id 
                AND l.company_id = s.company_id
              WHERE s.company_id = 22 ) AS data
        ON data.year = yrs.yr
       AND data.m = mths.mth
     WHERE yrs.yr*100+mths.mth BETWEEN 201203 AND 201207
    ORDER 
        BY y.yr
         , m.mth
         , data.name
    note i removed your GROUP BY clause because you don't seem to be aggregating anything
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some good stuff here. Thank you always for your help, Rudy. Sorry about the Group By. The sql I posted was a stripped down/simplified version of the production query. Scores are actually a compilation of several columns, which is why the Group By was originally included.


Bookmarks

Posting Permissions

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