时间戳

首页 > 解决方案 > Only include employees currently working for company

sql - Only include employees currently working for company

问题描述

Thanks to the input I've received earlier on this site, I use the below code to summarize how many months of experience our employees have in a specific market.

The issue however is that the end-result shows the summarized data for all employees, even if they no longer work for our company. What I actually would like, is the same output, but then only for those employees who are still present in the youngest date (SNAPSHOT_DATE).

I managed to get to a solution where I manually update the snapshot every month, but I'd rather go for an automated solution where the code itself determines what the youngest snapshot is.

Many thanks for your support :)

SELECT EMPLOYEE_ID,
ISNULL([Developing & Emerging],0) AS [Experience - D&E],
ISNULL([Developed],0) AS [Experience - D]
FROM 
(
    SELECT EMPLOYEE_ID,MARKET_TYPE_DESC,COUNT(SNAPSHOT_DATE)  T
    FROM [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL]
  WHERE ALLC_SER_NUM = '1'
  GROUP BY EMPLOYEE_ID,MARKET_TYPE_DESC
)P
PIVOT(
    SUM(T)
    FOR MARKET_TYPE_DESC IN ([Developing & Emerging],[Developed])
)PVT

标签： sqlsql-server

解决方案

听起来您正在尝试将查询减少到仅返回 [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL].SNAPSHOT_DATE == 最新快照日期的结果。

有多种方法可以实现这一点，但可能最简单和最容易阅读的方法如下：

DECLARE @SnapshotDate DATETIME
 SELECT @SnapshotDate = MIN(SNAPSHOT_DATE ) FROM [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL]

然后通过在主查询中加入 CTE，使用 CTE 将包含的员工列表减少到仅具有与此快照日期匹配的记录的员工：

;WITH CTE AS
(
    SELECT EMPLOYEE_ID
      FROM [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL]
     WHERE SNAPSHOT_DATE = @SnapshotDate 
)
 SELECT EMPLOYEE_ID,
    ISNULL([Developing & Emerging],0) AS [Experience - D&E],
    ISNULL([Developed],0) AS [Experience - D]
    FROM 
    (
        SELECT ED.EMPLOYEE_ID,ED.MARKET_TYPE_DESC,COUNT(ED.SNAPSHOT_DATE)  T
        FROM [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL] ED
        JOIN CTE C ON C.EMPLOYEE_ID = ED.EMPLOYEE_ID
      WHERE ED.ALLC_SER_NUM = '1'
      GROUP BY ED.EMPLOYEE_ID,ED.MARKET_TYPE_DESC
    )P
    PIVOT(
        SUM(T)
        FOR MARKET_TYPE_DESC IN ([Developing & Emerging],[Developed])
    )PVT

推荐阅读