CODEWITHSUNDEEP
sqlsql-servert-sqlouter-apply
Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Outer Column reference in an aggregate function of a cross apply

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

  1. Number of Employees Joined in each day of a period
  2. Number of Employees Resigned in each day of a period
  3. 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 )

Upvotes: 0

Views: 1561

Answers (1)

iamdave
iamdave

Reputation: 12243

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

Upvotes: 1

Related Questions