Here’s an option using the window function row_number() and a conditional aggregation

Example

with cte as ( 
Select *
      ,Grp = row_number() over (partition by [Student ID],Action order by [Effective Date])
 From  @YourTable 
)
Select [Student ID]
      ,[Leave Date]  = max(case when Action='Leave'  then [Effective Date] end)
      ,[Return Date] = max(case when Action='Return' then [Effective Date] end)
 From cte 
 Group By [Student ID]
         ,Grp
 Order By [Student ID],[Leave Date]

Results

Student ID  Leave Date  Return Date
A1          2024-01-01  2024-03-01
A1          2024-06-01  2024-08-01
A2          2024-01-01  2024-05-01
A3          2025-01-01  NULL

here is one way including the absence_count :

with cte as (
  select *
  , row_number() over (partition by student_id,action order by effective_date) as rn
  from temp_student_actions
  ) 

select student_id 
  , count(*) over (partition by student_id rows unbounded preceding) no_of_absence
  , max(case when action='Leave' then effective_date end) leave_date
  , max(case when action='Return' then effective_date end) return_date 
from cte 
group by student_id, rn 
order by student_id, leave_date

output:

fiddle

Yes, there is always a way to achieve this. Here is how it can be done by using the combination of ROW_NUMBER() and LAG() functions in the SQL server.

Here is the query

WITH Absences AS (
  SELECT StudentID, Action, EffectiveDate,
         ROW_NUMBER() OVER (PARTITION BY StudentID, Action ORDER BY EffectiveDate) AS RowNum,
         CASE Action WHEN 'Leave' THEN 1 ELSE -1 END AS ActionSign
  FROM YourTable
)
SELECT StudentID,
       SUM(ActionSign) OVER (PARTITION BY StudentID ORDER BY EffectiveDate) AS AbsenceNumber,
       CASE Action WHEN 'Leave' THEN EffectiveDate END AS LeaveDate,
       LAG(CASE Action WHEN 'Return' THEN EffectiveDate END, 1, NULL) OVER (PARTITION BY StudentID ORDER BY EffectiveDate) AS ReturnDate
FROM Absences
WHERE Action IN ('Leave', 'Return')
ORDER BY StudentID, EffectiveDate;

The first part is CTE I called it Absences assigns a row to each row within each student’s leave/return sequence using ROW_NUMBER()

The main query uses a window function (SUM and OVER) to calculate the absence number for each student.

The CASE statement filters and LAG function is use to get the previous data.

Here is the output.

StudentID  AbsenceNumber  LeaveDate    ReturnDate
A1        1             2024-01-01   2024-03-01
A1        2             2024-06-01   2024-08-01
A1        3             2025-01-01   2025-06-01
A2        1             2024-01-01   2024-05-01