I need some help one getting the project count per month for all of the projectTypes per region. I've tried the following statement but it didn't return what I need. The problem with the statement below is that it only returns the projectType only if there a count for that month. I need to return zero if the month doesn't have a count. Any help is much appreciated.
SELECT r.region, pt.projectType, count(p.id) as totalCount, p.postedOn as monthCount
FROM region r cross join ProjectTypes pt left join projects p on p.regionID = r.id and pt.id = p.TypeID
WHERE year(p.postedOn) = '2012'
group by r.region, pt.projectType, p.postedOn
order by r.region
Please the sample here: http://sqlfiddle.com/#!3/6680f/18
regions: ------------------------- id | region | ------------------------- 1 | East | ------------------------- 2 | MidWest | ------------------------- 3 | West | ------------------------- Project Type: ------------------------- id | projectType | ------------------------- 1 | Web Desgin | ------------------------- 2 | Database | ------------------------- 3 | Development | ------------------------- Projects: ------------------------------------------------------------------------- id | projectName | regionID | projectTypeID | postedOn | ------------------------------------------------------------------------- 1 | Project 1 | 1 | 2 | 2012-09-02 | ------------------------------------------------------------------------- 2 | Project 2 | 2 | 2 | 2012-09-02 | ------------------------------------------------------------------------- 3 | Project 3 | 1 | 1 | 2012-09-02 | ------------------------------------------------------------------------- 4 | Project 4 | 3 | 2 | 2012-09-02 | ------------------------------------------------------------------------- 5 | Project 5 | 3 | 1 | 2012-10-02 | ------------------------------------------------------------------------- 6 | Project 6 | 3 | 2 | 2012-10-02 | ------------------------------------------------------------------------- 7 | Project 7 | 3 | 3 | 2012-10-02 | ------------------------------------------------------------------------- 8 | Project 8 | 2 | 3 | 2012-10-02 | ------------------------------------------------------------------------- 9 | Project 9 | 1 | 2 | 2012-10-02 | ------------------------------------------------------------------------- 10 | Project 10 | 1 | 2 | 2012-10-02 | ------------------------------------------------------------------------- Desired Results: --------------------------------------------------------- Region | project Type | totalCount | monthCount | --------------------------------------------------------- East | Web Desgin | 1 | September | --------------------------------------------------------- East | Database | 1 | September | --------------------------------------------------------- East | Development | 0 | September | --------------------------------------------------------- Midwest | Web Desgin | 0 | September | --------------------------------------------------------- Midwest | Database | 1 | September | --------------------------------------------------------- Midwest | Development | 0 | September | --------------------------------------------------------- West | Web Desgin | 0 | September | --------------------------------------------------------- West | Database | 1 | September | --------------------------------------------------------- West | Development | 0 | September | --------------------------------------------------------- East | Web Desgin | 0 | October | --------------------------------------------------------- East | Database | 2 | October | --------------------------------------------------------- East | Development | 0 | October | --------------------------------------------------------- Midwest | Web Desgin | 0 | October | --------------------------------------------------------- Midwest | Database | 0 | October | --------------------------------------------------------- Midwest | Development | 1 | October | --------------------------------------------------------- West | Web Desgin | 1 | October | --------------------------------------------------------- West | Database | 1 | October | --------------------------------------------------------- West | Development | 1 | October | ---------------------------------------------------------
The WITH block defines a virtual table of months (or rather, the first days of each month) required. This extends your CROSS JOIN to give you all the months.
;WITH months(startdate) AS (
SELECT CAST('20120901' AS date)
UNION ALL
SELECT dateadd(m,1,startdate)
FROM months
WHERE startdate < '20121001'
)
SELECT r.region,
pt.projectType,
count(p.id) totalCount,
DATENAME(Month,m.startdate) monthCount
FROM region r
CROSS JOIN ProjectTypes pt
CROSS JOIN months m
LEFT JOIN projects p ON p.regionID = r.id
AND pt.id = p.TypeID
AND p.postedOn >= m.startdate
AND p.postedOn < dateadd(m,1,m.startdate)
GROUP BY r.region, pt.projectType, m.startdate
ORDER BY m.startdate, region, projecttype
OPTION (maxrecursion 0);
I applied this to your fiddle
For SQL Server 2000, or for performance really, create the table Months as a proper table and fill it with months from 1999 through the year 2169, e.g.
CREATE TABLE Months (
startdate datetime -- the first day of month
primary key
);
insert Months
select DateAdd(M,Number,'19990101')
from master..spt_values
where type='P'
GO
Then, just choose the month range you need using the condition against the months table, i.e. as below:
SELECT r.region,
pt.projectType,
count(p.id) totalCount,
DATENAME(Month,m.startdate) monthCount
FROM region r
CROSS JOIN ProjectTypes pt
JOIN months m on m.startdate between '20120901' and '20121001'
LEFT JOIN projects p ON p.regionID = r.id
AND pt.id = p.TypeID
AND p.postedOn >= m.startdate
AND p.postedOn < dateadd(m,1,m.startdate)
GROUP BY r.region, pt.projectType, m.startdate
ORDER BY m.startdate, region, projecttype;
Updated SQL Fiddle