How to get total cost per hotel and transfer per 8 days?

Question

Problem

How to get total cost per hotel and transfer per 8 days ?

Details

8 days = 7 nights

meaning 8 days = 7 days accommodation per hotel

because last day he take flight not stay in hotel .

Result i need to get it

 

why null display in fiddle i need result in one line as above

the database and query found in the following fiddle

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0096a903948a93c1269e931328648be2

this sq query used

;with cte_HotelPrice
as
(
select
T6.HotelPrice,
T4.HotelID,
T5.HotelName,
T3.DetailsDurationID from package T
inner join StartPackage T1 on T.PackageId=T1.PackageId
inner join packageduration T2 on T.PackageId=T2.PackageId
inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)
    FROM DurationDetails) T3 on T2.PackageDurationsId=T3.PackageDurationsID
inner join DayDetails T4 on T3.DetailsDurationID=T4.DetailsDurationID
left join Hotel T5 on T4.HotelID=T5.HotelID
cross apply (select HotelPrice from HotelPrice where HotelID=T4.HotelID and FromDate<=DATEADD(day, T3.RN - 1, T1.StartDate) and ToDate>=DATEADD(day, T3.RN - 1, T1.StartDate)) T6
)
,TransferPrice as
(
select
ttd.Price,
dds.DetailsDurationID
from package p
inner join StartPackage s on p.PackageId=s.PackageId
inner join packageduration pd on p.PackageId=pd.PackageId
inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)
    FROM DurationDetails) dd on pd.PackageDurationsId=dd.PackageDurationsID
inner join DayDetails dds on dd.DetailsDurationID=dds.DetailsDurationID
left join TransferType tt on dds.TransferTypeID=tt.TransferID
cross apply (select Price from TransferPeriod where TransferTypeID=dds.TransferTypeID and FromDate<=DATEADD(day, dd.RN - 1, s.StartDate) and Todate>=DATEADD(day, dd.RN - 1, s.StartDate)) ttd
)
select
S4.HotelID,S4.HotelName, S.PackageName, S1.StartDate, S1.EndDate,
sum(S4.HotelPrice) AS cost,
sum(S5.Price) as transfercost
from package S
inner join StartPackage S1 on S.PackageId=S1.PackageId
inner join packageduration S2 on S.PackageId=S2.PackageId
inner join DurationDetails S3 on S2.PackageDurationsId=S3.PackageDurationsID
left join  cte_HotelPrice S4 on S3.DetailsDurationID=S4.DetailsDurationID
left join  TransferPrice S5 on S3.DetailsDurationID=S5.DetailsDurationID
GROUP BY S4.HotelID, S4.HotelName,S.PackageName, S1.StartDate, S1.EndDate