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

Question

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 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 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

Cynthia Sathuragiri · Answer

Hi, Since the table dbo.Daydetails has the NULL value in HotelID column , the query produces the resultset with Null value as a seperate row. To avoid duplication map the dbo.Hotel table in the TransferPrice CTE as below. Hope it will be helpful to you.