BE_Joachim BE_Joachim - 3 months ago 6
SQL Question

Select duplicate by youngest date

I have run into a snag.
A got a DB with employees with multiple startdates.
Employees can start and can get a new contract later.

FE.

ID NAME DATEEMPLOYED FUNCTION
1 Paul 01/01/2016 Director
2 Paul 01/01/2015 Staff Member
3 Jeff 02/05/2016 Director
4 Jeff 01/05/2015 Employee
5 Jeff 01/05/2014 Employee
6 Eric 05/06/2015 Employee


Now I need to get the ID from the latest and the youngest date.
I want to copy the function of the row with the latest date to the oldest date and then delete all but the oldest.

The oldest I can find by:

SELECT * FROM [database].[dbo].[Personel] t WHERE DATEEMPLOYED NOT IN (SELECT MAX(DATEEMPLOYED) AS LastUpdate FROM [database].[dbo].[Personel] GROUP BY Naam,Voornaam)


This returns 10 rows...

Now to find the youngest...
I thought, it would be as easy as changing MAX(DATEEMPLOYED) to MIN(DATEEMPLOYED)...

But I guess not because this only returns 6 rows...

I'm running a live DB so no sample date...

The expected output of the query for the max date per employee is ID 1 and 3 ... The expected output for min date is ID 2 and 5 ...
No number 6

I'am running MS SQL trough an ASP.net application...
The query posted I'm running on the SQL server itself for testing...
Later I'll adapt for the ASP.Net
I want to automatize the deletion of duplicate employees.

Where did i go wrong?

Answer

DEPENDING on your version, you could use the window function

    Declare @YourTable table (ID int,NAME varchar(50),  DATEEMPLOYED Date, [FUNCTION] varchar(50))
    Insert Into @YourTable values
    (1,'Paul','01/01/2016','Director'),
    (2,'Paul','01/01/2015','Staff Member'),
    (3,'Jeff','02/05/2016','Director'),
    (4,'Jeff','01/05/2015','Employee'),
    (5,'Jeff','01/05/2014','Employee'),
    (6,'Eric','05/06/2015','Employee')


;with cteBase as (
    Select Distinct Name 
          ,Times   = count(*) over  (Partition By Name) 
          ,MinID   = min(ID) over (Partition By Name) 
          ,MaxID   = max(ID) over (Partition By Name) 
          ,MinDate = min(DATEEMPLOYED) over  (Partition By Name Order By DATEEMPLOYED) 
          ,MaxDate = max(DATEEMPLOYED) over  (Partition By Name Order By DATEEMPLOYED Desc) 
     From @YourTable
)
Select * from cteBase where Times>1 

Returns

Name    Times   MinID   MaxID   MinDate     MaxDate
Jeff    3       3       5       2014-01-05  2016-02-05
Paul    2       1       2       2015-01-01  2016-01-01