SQL Server combine overlapping date ranges

I am using SQL Server version 2014. I am working on requirement where I have find consecutive dates and paid fee based on the dates. For instance, the SESSION_ID 7415 as the dates are not overlapping the query works as expected. The only requirement is if for any reason the consecutive dates have different paid fees based on the latest SESSION_ENTER_DATE we should consider that particular PAID value. For the SESSION_ID 7415 the START_DATE 2018-12-09 - END_DATE 2018-12-29 though this date range has the fees PAID value of 37.02 & 63.6 but based on the latest SESSION_ENTER_DATE it would be 63.6.

Previous Query

But, the problem I run into is when there are overlapping dates. For instance, SESSION_ID 8642 has consecutive START_DATE & END_DATE 2022-02-17 - 2022-05-12 & another set 2022-06-02 - 2022-07-27 but the output I would want is 2/17/2022 - 9/7/2022 as the aforementioned date ranges would fall in between.

So for the SESSION_ID 8642 the output I would want to see is

We need to grab all the other records based on the START_DATE and if there are multiple START_DATE grab the one with the latest SESSION_ENTER_DATE by checking if the date range is consecutive or if there are any gaps in-between.

Modified code:

This is gaps and islands task.
As a rule, we compare end_date for current row and start_date for next row.
Or compare start_date with previous end_date.
If datediff(...)>1 then this is gap.

Since we are comparing the current row with the next one, we use a range of rows in the window function as rows between unbounded preceding and 1 preceding for calculating group number (running total).

Additional check is max_enter_date<next_start_date for current row. If not - this is overlapped row, not gap. It is for this check that we compare the current row with the next one, and not the other way around.

with gaps as ( -- detect gaps
select * 
  ,case when max_enter_date<next_start_date 
       and datediff(day,end_date
                         ,next_start_date
             )>1
   then 1 else 0 end isGap
from(
  select *
    ,lead(start_date,1,end_date)
         over(partition by session_id,session_type order by start_date) next_start_date
    ,max(case when session_enter_date is null then getdate() else session_enter_date end)
        over(partition by session_id,session_type order by start_date) max_enter_date
  from session
)a
)
,t as( -- detect last paid -> rn=1
select *
  ,row_number()over(partition by session_id,session_type,grn order by start_date desc)rn
from( -- calculate group (island) num as running total
  select *
    ,coalesce(sum(isGap)over(partition by session_id,session_type order by start_date
        rows between unbounded preceding and 1 preceding) 
      ,0) grn
  from gaps
 )grn
)
select session_id,session_type,grn
  ,min(start_date) start_date
  ,max(end_date) end_date
  ,max(case when rn=1 then paid end)paid
from t
group by session_id,session_type,grn
;

fiddle

An additional assumption is used here

case when session_enter_date is null then getdate() else session_enter_date end

if session_enter_date is null, this payment has not been used and is valid now.