This is to produce monthly one. I am working on Quarterly one and update you soon ( I have updated it below). I am using a temporary table that you have to change to your table and columns.
SELECT [Month],COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang
FROM
(
SELECT DATENAME(MONTH,MonthCol) [Month],
DATEPART(MONTH,MonthCol) [MonthNo],
Ab_Zugang,
Count(NoProjects) NoProjects
FROM #ProjectData
GROUP BY DATENAME(MONTH,MonthCol),DATEPART(MONTH,MonthCol), Ab_Zugang
) proj
PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
ORDER BY MonthNo
The output is
Month Zugang Abgang
January 2 0
February 0 1
March 2 0
May 0 1
June 1 0
July 1 0
September 1 0
November 1 0
December 0 1
This is for Quarterly one.
SELECT 'Q'+cast([Month_Quarter] as varchar) Month_Quarter,COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang
FROM
(
SELECT DATEPART(QUARTER,MonthCol) [Month_Quarter],
Ab_Zugang,
Count(NoProjects) NoProjects
FROM #ProjectData
GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
) proj
PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
ORDER BY Month_QuarterThe output is
The output is
Month_Quarter Zugang Abgang
Q1 4 1
Q2 1 1
Q3 2 0
Q4 1 1