Outer Column reference in an aggregate function of a cross apply

Question

In my query, I am using OUTER APPLY to get employee count in different scenarios like

Number of Employees Joined in each day of a period
Number of Employees Resigned in each day of a period
Number of employees leave on each day of a period... etc

Expected output (From:2017-01-10 to 2017-01-12 ) is

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

Below is my query

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

But while executing my query, getting the below error.

Msg 8124, Level 16, State 1, Line 18 Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Here the column [JOIN_COUNT] only producing the error, without this column the query is working. But i have more column pending to add like [JOIN_COUNT] (eg Resigned_Count, ...etc )

Is this in one of the pieces of SQL you are not showing us? The .... parts? — Cato, Jan 12 '17 at 11:59
it seems to be related to this http://sqlmag.com/blog/aggregates-outer-reference - their SQL also looked reasonable - could you perform each employee count as a separate section of the WITH statement, then SELECT a grand query of them at the end? — Cato, Jan 12 '17 at 12:03
@Cato, if i remove the 'COUNT(CASE WHEN [EMP_DOJ] = [CDATE] THEN 1 ELSE NULL END) AS [JOIN_COUNT]', i will get the query executed with the expected output. But JOIN_COUNT only the error (but i have more columns pending like this JOIN_COUNT, as RESIGN_COUNT, JUNIOR_JOIN_COUNT '... — Abdul Rasheed, Jan 12 '17 at 13:07

score 1 · Answer 1 · answered Jan 12 '17 at 12:18

You do not need an outer apply to achieve this, simply join your CTE_DATE valus to your employee table and use a sum(case when <Conditions met> then 1 else 0 end) with a group by the 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

Outer Column reference in an aggregate function of a cross apply

1 Answers1