Trouble understanding query behavior - help appreciated.

[diverguy]

Good Morning,

I'm really confused and could use some insight. This query involves two tables in a SQL2005 database. The tables are tblCalendarMonths and tblProjects. tblCalendarMonths consists of columns for a rowID, a startdate, an enddate, a month number (1-12), the year, and the name of the month spelled out - called monthtext. The relevant columns in tblProjects DateRequested, which is a date format, and ProjectTeam, which is an integer identifying which team is handling it. I need to be able to count the projects in a given month and filter by year and by team so that each month is displayed, even if that month's count is zero, and the counts for all of the months are only for the specified team. This query very neatly displays all of the months for the year and the counts for each month, even those that are zero:

SELECT a.monthtext AS [Request Month], COUNT(b.DateRequested) AS [Total Requests], a.monthno AS MonthNumber
FROM tblCalendarMonths AS a LEFT OUTER JOIN
tblProjects AS b ON b.DateRequested >= a.startdate AND b.DateRequested < a.enddate
WHERE (a.yearno = 2009)
GROUP BY a.monthtext, a.monthno
ORDER BY MonthNumber, [Request Month], [Total Requests]

It yields a datatable that looks like this (The third column is the month number, which the results are sorted by to keep it all in proper order):

January 0 1
February 14 2
March 3 3
April 0 4
May 1 5
June 0 6
July 0 7
August 0 8
September 10 9
October 0 10
November 0 11
December 0 12

When I try to modify the WHERE clause to filter by Project Team, it only returns the months that actually have a count, not those that are zero. Here's the modified query:

SELECT a.monthtext AS [Request Month], COUNT(b.DateRequested) AS [Total Requests], a.monthno AS MonthNumber
FROM tblCalendarMonths AS a LEFT OUTER JOIN
tblProjects AS b ON b.DateRequested >= a.startdate AND b.DateRequested < a.enddate
WHERE (a.yearno = 2009) AND (b.ProjectTeam = 1)
GROUP BY a.monthtext, a.monthno
ORDER BY MonthNumber, [Request Month], [Total Requests]

It yields this datatable:

February 11 2
March 3 3
May 1 5
September 10 9

Any ideas on why adding that one filter in the WHERE clause is causing this result? How should the query be modified to make it run correctly? Any help will be appreciated.

Thanks.

John

[diverguy]

Found the answer. I moved the "ProjectTeam = 1" filter to the ON statement and now it works just dandy. Thanks anyway.