Right Join / Group by query

Question

I have a (SQL Server) Right Join / Group by query designed to return all weeks of the year (week beginning) with a count of the number of projects that are delivered within each week. The query runs against two tables; WeeksOfyear (where the field Week_Beginning is the date of the first Monday of each week) and projects (where project.Target_Date_Week_Beginning contains the week starting date in which the project is targeted to deliver.) As shown below:

WeeksOfyear

ID  Week_Beginning

1   2013-12-29

2   2014-01-05

3   2014-01-12

etc.

PROJECT

ID  Name                        Target_Date_Week_Beginning  FK_Programme

1   1234-Smith Street           2014-06-29                  4
2   Marge Lane                  2014-07-20                  4
3   1234 Smith Street2          2014-10-26                  3
4   Marge Lane (Branch Design)  2015-11-01                  null
5   Papertray                   2014-11-02                  1
6   OpenSalad                   014-09-28                   1
7   Leamington Pie              2014-11-30                  1

The intention of the query is to return all weeks of the year and for each week count the number projects being delivered that week that belong to a specified programme (FK_Programme (as a parameter @ProgrammeParamater)).

    SELECT       dbo.WeeksOfyear.Week_Beginning, COUNT(dbo.Project.ID) AS Total_Projects
    FROM            dbo.Project right JOIN 
                  dbo.WeeksOfyear ON dbo.Project.Target_Date_Week_Beginning = dbo.WeeksOfyear.Week_Beginning
GROUP BY dbo.WeeksOfyear.Week_Beginning, dbo.Project.FK_Programme, dbo.Project.ID
HAVING        (dbo.Project.FK_Programme = @ProgrammeParamater) 
           or     (dbo.Project.ID  IS NULL)          
ORDER BY dbo.WeeksOfyear.Week_Beginning

However when the query is run the results returned exclude weeks of the year in which projects are delivered but are not part of the specified programme. Strangely the query returns any week in which no projects are delivered. If there are 52 records in the WeeksOfYear table I need the query to return 52 records.

I know that I could select every row from the WeeksOfyear table and do a sub-query to count the projects due that week but this seems an incredibly inefficient way to do this.

This has me stumped, any help much appreciated.

Thanks

Adam

Answer 1

I find queries easier to follow when they use left join and table aliases. In your case, you want to move the condition in the having clause all the way up to the on clause, so you are matching only projects in the right program.

SELECT w.Week_Beginning, COUNT(p.ID) AS Total_Projects
FROM dbo.WeeksOfyear w LEFT JOIN
     dbo.Project p 
     ON p.Target_Date_Week_Beginning = w.Week_Beginning AND
        p.FK_Programme = @ProgrammeParameter
GROUP BY w.Week_Beginning      
ORDER BYw.Week_Beginning;

You also don't want p.Id in the group by, if that is what you are counting.