sql - Group Employees By Year Then By Cost Center

Question

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.

Answer 1

Hmmm . . . You seem to want conditional aggregation. Without sample data, some of these details might be wrong. But the basic idea is:

SELECT cc.costCenterId,
       SUM(CASE WHEN th.workStartDate < '2021-01-01' and   
                     (th.workEndDate >= '2020-01-01' or th.workEndDate IS NULL)
                THEN 1 ELSE 0
           END) as yr_2020,
       SUM(CASE WHEN th.workStartDate < '2020-01-01' and   
                     (th.workEndDate >= '2019-01-01' or th.workEndDate IS NULL)
                THEN 1 ELSE 0
           END) as yr_2019,
          . . .
FROM teammateHistory th INNER JOIN
     costCenters cc
     ON th.costCenterId = cc.costCenterId
GROUP BY cc.costCenter;