Calculating a value by summing several rows

Query parts

1. CTE suitable - select all rows, intersecting period (dateFrom,dateTo) (2025-01-05->2025-04-01 in example) and rename column for for brevity and convenience.
2. Recursive query anchor part: take all rows, covering the beginning of the period (fromDate). Main output is (iFrom,iTo) - period, intersection of (fromDate,endDate) and(periodBegin, periodEnd)
3. Recursion on join anchor and added rows from cte suitable with intersection (iFrom,iTo) and (periodBegin,periodEnd).
4. Output is (list of room ids),(list of days in appropriate room),(list of price for this room) and total sum.

If recursive query output row (iFrom,iTo)= (fromDate,toDate) - this the desired result.

Fiddle with additional test data and details

In fact, there are many more possible combinations. After all, we can take periods with an offset of 1 day. I took only boundary cases.
See example. We have two segments (123456) and (4567). We need to place such a segment (3456) in them.

A   123456
      ||||
B      4567
Possible combinations:
A(3456)B()
A(3)B(456)
A(34)B(56)
A(345)B(6)

A(3)B(4)A(5)B(6)
A(34)B(5)A(6)
... and so on
A will take this case
A(3456)    - first result
A(3)B(456) - second result

See example:

with suitables as(
select Id,HotelCode,AccomodationPeriodBegin PeriodBegin
        ,AccomodationPeriodEnd  PeriodEnd
        ,dateFrom,dateTo
        ,RoomType,Price,AccmdMenTypeCode TypeCode
        ,datediff(day,dateFrom,dateTo)+1 totDays
from HotelAccoMmodation h
cross join (select cast('2025-01-05' as date) dateFrom
     , cast('2025-04-01' as date) dateTo)p
where RoomType in('DGV') -- AccmdMenTypeCod RoomType
  and AccmdMenTypeCode in ('01010003','01010312') -- suitable AccmdMenTypeCode
  and ( (AccomodationPeriodBegin<=dateTo and AccomodationPeriodEnd>=dateFrom)
       or (AccomodationPeriodBegin<=dateTo and AccomodationPeriodEnd>=dateFrom)
     )
)

The actual recursive query. I will use cross apply just to simplify calculations and expressions. We save the days, prices, and amounts in paths one step later in the recursive query, because in the next step we can reduce the number of days in the previous step.

,r as(
  select 1 lvl,Id root,hotelcode,Id,cast(concat('',Id) as varchar(1000)) pathIds
    ,dateFrom iFrom
    ,case when periodEnd>=dateTo then dateTo else periodEnd end iTo
    ,case when periodEnd>=dateTo then 0 
     else datediff(day,periodEnd,dateTo) end restDays
    ,price,periodBegin,periodEnd,dateFrom,dateTo
    ,ciTo,ddif
    ,cast(concat('','') as varchar(1000)) pathDays,cDays
    ,cast(concat('',price) as varchar(1000)) pathPrice
    ,cDays*price as total
  from suitables t
  cross apply( values(
       ( case when t.periodEnd>=t.dateTo then t.dateTo else t.periodEnd end )
     ,( datediff(day,t.dateFrom
           ,case when t.periodEnd>=t.dateTo then t.dateTo else t.periodEnd end) +1)
     ,(0)
         ))c(ciTo,cDays,ddif)
  where periodBegin<=dateFrom and periodEnd>=dateFrom
  union all
  select lvl+1 lvl,r.root,r.hotelcode,t.Id,cast(concat(pathIds,':',t.Id) as varchar(1000)) path
    ,t.periodBegin iFrom
    ,c.ciTo iTo
    ,case when t.periodEnd>=r.dateTo then 0 
     else datediff(day,c.ciTo,r.dateTo) 
     end restDays
    ,t.price,t.periodBegin,t.periodEnd,r.dateFrom,r.dateTo
    ,c.ciTo,c.ddif
    ,cast(concat(pathDays,':',r.cDays-c.ddif) as varchar(1000)) pathDays,c.cDays
    ,cast(concat(pathPrice,':',t.price) as varchar(1000)) pathPrice
    ,r.total -r.price*c.ddif+ c.cDays*t.price total
  from r
  inner join suitables  t on r.hotelcode=t.hotelcode
   and ( 
            t.periodBegin<=iTo and t.periodEnd>=iTo
        and t.periodBegin>r.iFrom
    )
  cross apply( values(
       ( case when t.periodEnd>=r.dateTo then r.dateTo else t.periodEnd end )
     ,( datediff(day,t.periodBegin
           ,case when t.periodEnd>=r.dateTo then r.dateTo else t.periodEnd end) +1)
     ,( datediff(day,t.periodBegin,r.iTo)+1 ) 
         ))c(ciTo,cDays,ddif)
  where lvl<7
)
select lvl, root, Id, pathIds, concat(pathDays,':',cDays) pathDays,pathPrice,total
from r 
where iTo=dateTo
order by pathIds,lvl

There, for example row

roomId 11 days 60 price 109
roomId 3 days 27 price 106
total 9402.

More details:

with suitables as(
select Id,HotelCode,AccomodationPeriodBegin PeriodBegin
        ,AccomodationPeriodEnd  PeriodEnd
        ,dateFrom,dateTo
        ,RoomType,Price,AccmdMenTypeCode TypeCode
        ,datediff(day,dateFrom,dateTo)+1 totDays
from HotelAccoMmodation h
cross join (select cast('2025-01-05' as date) dateFrom
     , cast('2025-04-01' as date) dateTo)p
where RoomType in('DGV') -- AccmdMenTypeCod RoomType
  and AccmdMenTypeCode in ('01010003','01010312') -- suitable AccmdMenTypeCode
  and ( (AccomodationPeriodBegin<=dateTo and AccomodationPeriodEnd>=dateFrom)
       or (AccomodationPeriodBegin<=dateTo and AccomodationPeriodEnd>=dateFrom)
     )
)
,r as(
  select 1 lvl,Id root,Id,cast(concat('',Id) as varchar(1000)) pathIds
    ,dateFrom iFrom
    ,case when periodEnd>=dateTo then dateTo else periodEnd end iTo
    ,case when periodEnd>=dateTo then 0 
     else datediff(day,periodEnd,dateTo) end restDays
    ,price,periodBegin,periodEnd,dateFrom,dateTo
    ,ciTo,ddif
    ,cast(concat('','') as varchar(1000)) pathDays,cDays
    ,cast(concat('',price) as varchar(1000)) pathPrice
    ,cDays*price as total
  from suitables t
  cross apply( values(
       ( case when t.periodEnd>=t.dateTo then t.dateTo else t.periodEnd end )
     ,( datediff(day,t.dateFrom
           ,case when t.periodEnd>=t.dateTo then t.dateTo else t.periodEnd end) +1)
     ,(0)
         ))c(ciTo,cDays,ddif)
  where periodBegin<=dateFrom and periodEnd>=dateFrom
  union all
  select lvl+1 lvl,r.root,t.Id,cast(concat(pathIds,':',t.Id) as varchar(1000)) path
    ,t.periodBegin iFrom
    ,c.ciTo iTo
    ,case when t.periodEnd>=r.dateTo then 0 
     else datediff(day,c.ciTo,r.dateTo) 
     end restDays
    ,t.price,t.periodBegin,t.periodEnd,r.dateFrom,r.dateTo
    ,c.ciTo,c.ddif
    ,cast(concat(pathDays,':',r.cDays-c.ddif) as varchar(1000)) pathDays,c.cDays
    ,cast(concat(pathPrice,':',t.price) as varchar(1000)) pathPrice
    ,r.total -r.price*c.ddif+ c.cDays*t.price total
  from r
  inner join suitables  t on 
    ( 
            t.periodBegin<=iTo and t.periodEnd>=iTo
        and t.periodBegin>r.iFrom
    )
  cross apply( values(
       ( case when t.periodEnd>=r.dateTo then r.dateTo else t.periodEnd end )
     ,( datediff(day,t.periodBegin
           ,case when t.periodEnd>=r.dateTo then r.dateTo else t.periodEnd end) +1)
     ,( datediff(day,t.periodBegin,r.iTo)+1 ) 
         ))c(ciTo,cDays,ddif)
  where lvl<7
)
-- select * from r
select *
from r 
-- where iTo=dateTo
order by pathIds,lvl

fiddle