Forum Discussion

Sach-SG's avatar
Sach-SG
Copper Contributor
Apr 01, 2025
Solved

How to create a view with unique record based on date

I have an EMPLOYEE table that looks like this:

EmployeeID,Name,PositionID,HireDate,PositionStatus
E001,Chelsey,P123,2013-11-23,Terminated
E001,Chelsey,P234,2019-03-13,Active
E002,Mike,P345,2014-03-23,Terminated
E002,Mike,P345,2021-10-13,Terminated
E003,James,P543,2015-01-12,Active
E004,Samantha,P895,2018-12-13,Terminated
E004,Samantha,P430,2020-05-21,Terminated
E004,Samantha,P945,2022-06-16,Active
E005,Kayla,P459,2023-03-13,Terminated

 

As you can see, the EmployeeID can have duplicate. The reason for this is, there's a secondary ID called PositionID, which essentially describes the employee's role. For example, whenever an employee gets promoted, or moves to a different department, a new record will be created for them with the same EmployeeID but a different PositionID. When that happens, the Position Status of the older role gets set to Terminated, and the new one gets set to Active. The HireDate represents the date teh employee started working in that role.

 

I would like to create a VIEW to filter this to have only the unique employees, based on the HireDate. If there are multiple records for the same employee, I want the view to show only the record with the most recent HireDate.

 

The resulting table should look like this:

 

 

 

 

 

What's the best way to go about this?

  • olafhelper's avatar
    olafhelper
    Apr 02, 2025

    Next time please post table design as DDL, some sample data as DML statement and the expected result.

    Group on EmployeeID to get the highest hire date and then join on.

    ;WITH cte AS
    (SELECT EmployeeID, MAX(HireDate) AS MaxHireDate
     FROM yourTable
     GROUP BY EmployeeID)
SELECT *
FROM cte
     INNER JOIN
     yourTable AS T
         ON cte.EmployeeID = T.EmployeeID
            AND cte.MaxHireDate = T.HireDate

     

2 Replies

  • divyed's avatar
    divyed
    Copper Contributor
    Apr 10, 2025

    Hello Sach-SG ,

     

    Here is SQL script to create a view :

     

    CREATE VIEW dbo.Sample_View
    AS
        Select [EmployeeID]
              ,[Name]
              ,[PositionID]
              ,[HireDate]
              ,[PositionStatus]
        From 
        (
        SELECT [EmployeeID]
              ,[Name]
              ,[PositionID]
              ,[HireDate]
              ,[PositionStatus],
              RowID=ROW_NUMBER() OVER (Partition by EmployeeID Order by HireDate Desc)
          FROM [Test].[dbo].[Employee]
        ) As A Where RowID=1

     

    Here is our output :

     

    I hope this answers your query.

     

    Did I answer your query ? Mark this as solution if this helps , Kudos are appreciated.

     

    Kind Regards,

    Neeraj

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Apr 02, 2025

    Next time please post table design as DDL, some sample data as DML statement and the expected result.

    Group on EmployeeID to get the highest hire date and then join on.

    ;WITH cte AS
    (SELECT EmployeeID, MAX(HireDate) AS MaxHireDate
     FROM yourTable
     GROUP BY EmployeeID)
SELECT *
FROM cte
     INNER JOIN
     yourTable AS T
         ON cte.EmployeeID = T.EmployeeID
            AND cte.MaxHireDate = T.HireDate

     

Resources