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?
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
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
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
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.