I have a table of employees that includes their start date, end date and cost center. Employees have a many-to-one relationship to cost centers.
I need to create a query that counts employees at the end of each year, and within each year by cost center, so the result set might look like this:
I have no problem getting a count of all employees for the entire organization by year using this query (fiscalYear is just a list of years from fiscalCalender):
SELECT x.fiscalYear ,
( SELECT Count(y.teammateId)
FROM teammateHistory y inner join costCenters cC on y.costCenterId = cC.costCenterId
WHERE year(y.workStartDate) <= x.fiscalYear
and y.workEndDate is null
) AS Headcount
FROM fiscalCalendar x
group by x.fiscalYear
Just can't figure out how to add a sub-count grouped by cost center for each fiscal year.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…