sql

SQL to show current PTID and Next_PTID based on my example

I would like to write SQL based on the example & expected result below.

Example & Expected outcome

I tried below SQL but not able to receive the expected result.

select 
  a.TeamPlanningID,
  case when b.[Start Date] is not null and [End Date] is null then a.[PTID] END as [PTID]   
 ,case when b.[Start Date] is null and [End Date] is null then a.[PTID] END as Next_PTID    
from  
  [TableA] a 
inner join [TableB] c on a.ConsultantsRefDataIDs = b.ID
where a.TeamPlanningID = 36
Solution 
  • select TeamPlanningID, ConsultantsRefDataIDs, PTID, 
  lead(PTID) over (partition by TeamPlanningID order by ConsultantsRefDataIDs) as Next_PTID
from tableA
order by TeamPlanningID, ConsultantsRefDataIDs;