CODEWITHSUNDEEP
sqlsql-server
Nipius
Nipius

Reputation: 27

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

Upvotes: 0

Views: 127

Answers (2)

James S
James S

Reputation: 171

It sounds like you are trying to reduce your query down to only return results where [db_name].[AGL_V_HRA_FE_R].[VW_HRA_EMPLOYEE_DETAIL].SNAPSHOT_DATE == the latest snapshot date.

There are multiple ways you can achieve this, but probably the simplest and most easily readable would be something like the following:

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

And then use a CTE to reduce the included list of employees to only those which have a record which matches this snapshot date, by joining the CTE in your main query:

;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

Upvotes: 1

Nipius
Nipius

Reputation: 27

Thanks, @James S! Much appreciated :)

Perhaps a stupid question, but should I just create one query combining both your inputs? Looking like the one below?

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

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 EMPLOYEE_ID,MARKET_TYPE_DESC,COUNT(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 ALLC_SER_NUM = '1'
        AND SNAPSHOT_DATE = @SnapshotDate
      GROUP BY EMPLOYEE_ID,MARKET_TYPE_DESC
    )P
    PIVOT(
        SUM(T)
        FOR MARKET_TYPE_DESC IN ([Developing & Emerging],[Developed])
    )PVT

I tried this, but it seems that doesn't work as it gives the following reply:

Incorrect syntax near 'CTE'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.```

Upvotes: 0

Related Questions