Forum Discussion

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-...

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

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