Ссылка на внешний столбец в статистической функции перекрестного применения

Question

В моем запросе я использую OUTER APPLY чтобы получить количество сотрудников в различных сценариях, таких как

1. Количество сотрудников, зачисленных в каждый день периода
2. Количество работников, уволенных за каждый день периода
3. Количество сотрудников, уволенных в каждый день периода... и т. Д.

Ожидаемый результат (From:2017-01-10 to 2017-01-12) является

CDATE       TOTAL_COUNT JOIN_COUNT  RESIGNED _COUNT ...
2017-01-10  1204        10          2
2017-01-11  1212        5           1
2017-01-12  1216        3           0

Ниже мой запрос

DECLARE @P_FROM_DATE    DATE = '2017-01-01', --From 1st Jan
        @P_TO_DATE      DATE = '2017-01-10'  --to 10th jan

;WITH CTE_DATE
AS
(   
    SELECT  @P_FROM_DATE    AS CDATE
    UNION ALL
    SELECT  DATEADD(DAY,1,CDATE)
    FROM    CTE_DATE
    WHERE   DATEADD(DAY,1,CDATE)    <=  @P_TO_DATE
)
SELECT  [CDATE]
        ,[TOTAL_COUNT]
        ,[JOIN_COUNT]
FROM    CTE_DATE
OUTER APPLY (
        SELECT  COUNT(CASE WHEN [EMP_DOJ]  = [CDATE] THEN 1 ELSE NULL END) AS [JOIN_COUNT]
                ,COUNT(*)   AS  [TOTAL_COUNT]
                ,....
                ,...
        FROM    [EMPLOYEE_TABLE]
        )   AS  D

Но, выполняя мой запрос, получаю ошибку ниже.

Сообщение 8124, уровень 16, состояние 1, строка 18 Несколько столбцов указываются в агрегированном выражении, содержащем внешнюю ссылку. Если агрегируемое выражение содержит внешнюю ссылку, то эта внешняя ссылка должна быть единственным столбцом, на который ссылается выражение.

Здесь столбец [JOIN_COUNT] только выдает ошибку, без этого столбца запрос работает. Но у меня есть еще столбец в ожидании добавления как [JOIN_COUNT] (например, Resigned_Count, ... и т. д.)

Answer 1

Вам не нужно outer apply чтобы достичь этого, просто присоединяйтесь CTE_DATE valus к вашему столу сотрудника и использовать sum(case when <Conditions met> then 1 else 0 end) с group by CDate

select d.CDate
      ,sum(case when e.Emp_DoJ <= d.CDate
                  and e.EmployeeResignDate > d.CDate
                then 1
                else 0
                end) as Total_Count

      ,sum(case when e.Emp_DoJ = d.CDate
                then 1
                else 0
                end) as Join_Count

      ,sum(case when e.EmployeeResignDate = d.CDate
                then 1
                else 0
                end) as Resign_Count
from CTE_DATE d
    left join Employee_Table e
        on(d.CDate between e.Emp_DoJ and e.EmployeeResignDate)
group by d.CDate
order by d.CDate