仅对最新快照中存在的员工 ID 求和

Question

我有一个数据库，每个月都有一行，供在我们公司工作的每位员工使用。因此，如果员工 A 从 2016 年 7 月至今一直在我们公司工作，则该人大约有 24 行(她任职的每个月一行)。

我试图总结每个现有员工在特定职能中的经验。因此，如果员工 A 在销售部门工作了 6 个月，在市场营销部门工作了 18 个月，那么我会在指示该功能的列中计算该员工在 Sales 或 Marketing 方面的行数。

我创建了一个代码，它似乎确实计算了每位员工的功能体验，但它会重复计算数据。它不以最新快照为起点。

SELECT A.EMPLOYEE_ID,
SUM(CASE WHEN A.FUNCTION_CODE ='CUS' THEN 1 ELSE 0 END) AS EXP_CUS,
SUM(CASE WHEN A.FUNCTION_CODE ='MKT' THEN 1 ELSE 0 END) AS EXP_MKT
FROM [dbname].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL] AS A INNER JOIN [dbname].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL] AS B ON A.EMPLOYEE_ID = B.EMPLOYEE_ID
WHERE B.WORKLEVEL_CODE > '1'
GROUP BY A.EMPLOYEE_ID

我预计员工 A 的输出为 EXP_CUS = 6，EXP_MKT = 18。相反，两者的输出要高得多，因为它是重复计算行。当我添加行 AND B.SNAPSHOT_DATE = '2019-06-30' 时，输出是正确的。我不喜欢每个月手动调整代码，而是参考最新的快照日期。

已添加原始表如下所示

SNAPSHOT_DATE | EMPLOYEE_ID | FUNCTION_CODE
2019-06-30    | 000000001   | CUS
2019-06-30    | 000000002   | MKT
2019-05-31    | 000000001   | CUS
2019-05-31    | 000000002   | MKT
2019-04-30    | 000000001   | MKT
2019-04-30    | 000000002   | MKT

所需的输出将是

EMPLOYEE_ID   | EXP_CUS     | EXP_MKT
000000001     | 2           | 1
000000002     | 0           | 3

Answer 1

您可以使用 PIVOT 获得所需的结果，如下所示-

SELECT EMPLOYEE_ID,
ISNULL([CUS],0) AS [EXP_CUS],
ISNULL([MKT],0) AS [EXP_MKT]
FROM 
(
SELECT EMPLOYEE_ID,FUNCTION_CODE,COUNT(SNAPSHOT_DATE)  T
FROM your_table
GROUP BY EMPLOYEE_ID,FUNCTION_CODE
)P
PIVOT(
SUM(T)
FOR FUNCTION_CODE IN ([CUS],[MKT])
)PVT

输出是-

EMPLOYEE_ID EXP_CUS EXP_MKT
000000001   2       1
000000002   0       3

Answer 2

我不明白你为什么要使用自加入。 这似乎可以满足您的需求：

SELECT ED.EMPLOYEE_ID,
SUM(CASE WHEN ED.FUNCTION_CODE ='CUS' THEN 1 ELSE 0 END) AS EXP_CUS,
SUM(CASE WHEN ED.FUNCTION_CODE ='MKT' THEN 1 ELSE 0 END) AS EXP_MKT
FROM [dbname].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL] ed 
WHERE ED.WORKLEVEL_CODE > '1'
GROUP BY ED.EMPLOYEE_ID;

如果您只希望员工具有最近的快照日期，则可以使用窗口函数：

SELECT ED.EMPLOYEE_ID,
SUM(CASE WHEN ED.FUNCTION_CODE ='CUS' THEN 1 ELSE 0 END) AS EXP_CUS,
SUM(CASE WHEN ED.FUNCTION_CODE ='MKT' THEN 1 ELSE 0 END) AS EXP_MKT
(SELECT ED.*,
MAX(SNAPSHOT_DATE) OVER () as OVERALL_MAX_SNAPSHOT_DATE,
MAX(SNAPSHOT_DATE) OVER (PARTITION BY EMPLOYEE_ID) as EMPLOYEE_MAX_SNAPSHOT_DATE            
FROM [dbname].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL] ED
) ED
WHERE ED.WORKLEVEL_CODE > '1' AND
EMPLOYEE_MAX_SNAPSHOT_DATE = OVERALL_MAX_SNAPSHOT_DATE
GROUP BY ED.EMPLOYEE_ID;