Kinda pivot table thing question in SQL Server-CodePudding

I have a Staff table that includes staff previous promotion and title history.

earliest date in 'startDate' for a staff is the date of the he/she/apache started. lastest date of 'EndDate' for a staff is today. and that title is today's title.

I need to run a script that contains,

Id, Name, StartDate (as day he/she hired), enddate (lastest day of EndDate column) and Title (lastest title)

I tried something but no success..

Sample table as follows:

create table staff
(

  PersonId int,
  Name varchar(50),
  StartDate date,
  EndDate date,
  Title varchar(50)
  
);

insert into staff (PersonId,Name,StartDate,EndDate,Title) values
( '2798','Daniel','20200131','20200331','Assistant'),
( '2798','Daniel','20200401','20200630','Senior'),
( '2798','Daniel','20200701','20210331','Manager'),
( '553','Rebecca','20200131','20200430','Senior'),
( '553','Rebecca','20200501','20210331','Manager')

  
;

select * from staff;

DB Fiddle

CodePudding user response：

is that what you are looking for? Solution ist not optimized ;-)

;with cte_first_date (PersonID, first_startdate)
as 
    (select PersonId, MIN(startdate) as first_startdate from staff group by PersonId)

,cte_last_enddate (PersonID, last_enddate)
as 
    (select PersonId, MAX(enddate) as last_enddate from staff group by PersonId)

,cte_last_title (PersonID, last_title)
as
    (select a.PersonId, Title from #staff a join cte_last_enddate b on a.PersonId=b.PersonID and a.EndDate=b.last_enddate)

select distinct 
    a.PersonId, a.Name
    ,b.first_startdate
    ,c.last_enddate
    ,d.last_title
from staff a
    join cte_first_date b on a.PersonId=b.PersonId
    join cte_last_enddate c on a.PersonId=c.PersonId
    join cte_last_title d on a.PersonId=d.PersonID

CodePudding user response：

Calculate the start and end dates for each person, then join back to the original table on person and end date to get the final title:

;with empRange as (
SELECT PersonID, MIN(StartDate) AS firstStart, MAX(EndDate) AS lastEnd
FROM staff GROUP BY PersonID
)

SELECT
e.PersonID
,e.firstStart as StartDate
,s.EndDate
,s.Title
FROM empRange e
JOIN staff s ON e.PersonID = s.PersonID
AND s.EndDate = e.lastEnd