Kinda pivot table thing question in SQL Server

Question

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

*"I tried something but no success.."* then show us that "something"; if you don't show us what you tried, and tell us the reason it wasn't a "success", we can't explain *why* it didn't work. — Thom A, Jan 03 '23 at 14:37
In truth, however, this sounds like you want a [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group), not a pivot. — Thom A, Jan 03 '23 at 14:37

score 1 · Accepted Answer · answered Jan 03 '23 at 17:07

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

score 0 · Answer 2 · answered Jan 03 '23 at 15:21

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

This might work, but I will admit that I doubt it'll be efficient. It'll likely result in 4-5 scans of the table, *and* then you have the `DISTINCT` on top of that. — Thom A, Jan 03 '23 at 16:02
It's important to not just post code, but to also include a description of what the code does and why you are suggesting it. This helps others understand the context and purpose of the code, and makes it more useful for others who may be reading the question or answer. — DSDmark, Jan 03 '23 at 16:17

Kinda pivot table thing question in SQL Server

2 Answers2