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

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 image i need result in one line as above

this image screen shoot of wrong

I use following query

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

Sample database and data

``````USE [NileTraveltest]
GO
/****** Object:  Table [dbo].[DayDetails]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DayDetails](
[DayDetailsID] [nvarchar](50) NOT NULL,
[DetailsDurationID] [nvarchar](50) NULL,
[HotelID] [int] NULL,
[TransferTypeID] [nvarchar](50) NULL,
CONSTRAINT [PK_DayDetails] PRIMARY KEY CLUSTERED
(
[DayDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DurationDetails]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
[DetailsDurationID] [nvarchar](50) NOT NULL,
[PackageDurationsID] [nvarchar](50) NULL,
[Days] [nvarchar](50) NULL,
CONSTRAINT [PK_DurationDetails] PRIMARY KEY CLUSTERED
(
[DetailsDurationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Hotel]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotel](
[HotelID] [int] NOT NULL,
[HotelName] [nvarchar](50) NULL,
[Rating] [nvarchar](10) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[HotelPrice]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
[HotelPriceID] [nvarchar](50) NOT NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[HotelPrice] [decimal](18, 0) NULL,
[HotelID] [int] NULL,
CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED
(
[HotelPriceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Package]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Package](
[PackageID] [nvarchar](50) NOT NULL,
[PackageName] [nvarchar](100) NULL,
[Duration] [nvarchar](50) NULL,
[Resident] [tinyint] NULL,
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
[PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PackageDuration]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackageDuration](
[PackageDurationsID] [nvarchar](50) NOT NULL,
[PackageID] [nvarchar](50) NULL,
[PackageDuration] [int] NULL,
[NightCounts] [int] NULL,
CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED
(
[PackageDurationsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[StartPackage]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StartPackage](
[StartID] [nvarchar](50) NOT NULL,
[PackageID] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[TotalCost] [decimal](18, 0) NULL,
CONSTRAINT [PK_StartPackage] PRIMARY KEY CLUSTERED
(
[StartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TransferPeriod]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferPeriod](
[TransferDataID] [nvarchar](50) NOT NULL,
[FromDate] [datetime] NULL,
[Todate] [datetime] NULL,
[Price] [decimal](18, 0) NULL,
[TransferTypeID] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferPeriod] PRIMARY KEY CLUSTERED
(
[TransferDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TransferType]    Script Date: 22/07/2017 7:29:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferType](
[TransferID] [nvarchar](50) NOT NULL,
[TransferType] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferType] PRIMARY KEY CLUSTERED
(
[TransferID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD01', N'DD01', 1, N'T01')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD02', N'DD02', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD03', N'DD03', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD04', N'DD04', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD05', N'DD05', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD06', N'DD06', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD07', N'DD07', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD08', N'DD08', NULL, N'T02')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD01', N'PD01', N'DAY1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD02', N'PD01', N'DAY2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD03', N'PD01', N'DAY3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD04', N'PD01', N'DAY4')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD05', N'PD01', N'DAY5')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD06', N'PD01', N'DAY6')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD07', N'PD01', N'DAY7')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD08', N'PD01', N'DAY8')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD09', N'PD03', N'DAY1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD10', N'PD03', N'DAY2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD11', N'PD03', N'DAY3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD12', N'PD03', N'DAY4')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD13', N'PD03', N'DAY5')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD14', N'PD03', N'DAY6')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD15', N'PD03', N'DAY7')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD16', N'PD03', N'DAY8')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Movenpick', N'**')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'BasmaHotel', N'***')
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP01', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(20 AS Decimal(18, 0)), 1)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP02', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(30 AS Decimal(18, 0)), 1)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP03', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(30 AS Decimal(18, 0)), 2)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP04', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(40 AS Decimal(18, 0)), 2)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP05', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(50 AS Decimal(18, 0)), 3)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP06', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(60 AS Decimal(18, 0)), 3)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P02', N'AlexaPackage', N'8,15', 0)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P03', N'Amon', N'8', 1)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD01', N'P02', 8, 7)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD02', N'P02', 15, 14)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD03', N'P03', 8, 7)
INSERT [dbo].[StartPackage] ([StartID], [PackageID], [StartDate], [EndDate], [TotalCost]) VALUES (N'SD01', N'P02', CAST(0x0000A7A000000000 AS DateTime), CAST(0x0000A7A700000000 AS DateTime), CAST(210 AS Decimal(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD01', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD02', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD03', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T02')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD04', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T02')
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T01', N'from airport to hotel')
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T02', N'From Hotel to parking')
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_DurationDetails] FOREIGN KEY([DetailsDurationID])
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]
GO
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]
GO
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_TransferType]
GO
ALTER TABLE [dbo].[DurationDetails]  WITH CHECK ADD  CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])
REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]
GO
ALTER TABLE [dbo].[HotelPrice]  WITH CHECK ADD  CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]
GO
ALTER TABLE [dbo].[PackageDuration]  WITH CHECK ADD  CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Package] ([PackageID])
GO
ALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
GO
ALTER TABLE [dbo].[StartPackage]  WITH CHECK ADD  CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Package] ([PackageID])
GO
ALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]
GO
ALTER TABLE [dbo].[TransferPeriod]  WITH CHECK ADD  CONSTRAINT [FK_TransferPeriod_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferID])
GO
ALTER TABLE [dbo].[TransferPeriod] CHECK CONSTRAINT [FK_TransferPeriod_TransferType]
GO
``````

dbfiddle here

Update I run query in answer1 it give me correct result in first line only and two rows extra correct data

``````1 Hilton AlexaPackage 28-06-2017  05-07-2017 180  500
``````

only above record but another data is extra and not correct

Way overthinking it. The only halfway interesting part of the query should be exploding the dates to correctly reference the variable cost of the hotel rooms.

``````SELECT  h.HotelID, h.HotelName, p.PackageName, hc.StartDate, hc.EndDate,
hc.Cost, TransferCost = athc.Price + htpc.Price
FROM (  SELECT  h.HotelID, p.PackageID, sp.StartDate, sp.EndDate, Cost = SUM( hp.HotelPrice )
FROM    #t_StartPackage sp
INNER JOIN #t_Package p
ON  sp.PackageID = p.PackageID
CROSS APPLY (   SELECT  TOP ( DATEDIFF( DAY, sp.StartDate, sp.EndDate ) )
ActiveDate = DATEADD( DAY, ROW_NUMBER() OVER ( ORDER BY object_id ) - 1, sp.StartDate )
FROM    sys.objects
ORDER BY object_id ) d
CROSS JOIN #t_Hotel h
INNER JOIN #t_HotelPrice hp
ON  h.HotelID = hp.HotelID
AND d.ActiveDate >= hp.FromDate
AND d.ActiveDate <= hp.ToDate
GROUP BY h.HotelID, p.PackageID, sp.StartDate, sp.EndDate ) hc
INNER JOIN #t_Hotel h
ON  hc.HotelID = h.HotelID
INNER JOIN #t_Package p
ON  hc.PackageID = p.PackageID
INNER JOIN #t_TransferType ath
ON  ath.TransferType = 'from airport to hotel'
INNER JOIN #t_TransferPeriod athc
ON  ath.TransferID = athc.TransferTypeID
AND hc.StartDate >= athc.FromDate
AND hc.StartDate <= athc.Todate
INNER JOIN #t_TransferType htp
ON  htp.TransferType = 'From Hotel to parking'
INNER JOIN #t_TransferPeriod htpc
ON  htp.TransferID = htpc.TransferTypeID
AND hc.EndDate >= htpc.FromDate
AND hc.EndDate <= htpc.Todate;
``````

If you don't need it per hotel:

``````SELECT  h.HotelID, h.HotelName, p.PackageName, hc.StartDate, hc.EndDate,
hc.Cost, TransferCost = athc.Price + htpc.Price
FROM (  SELECT  nd.HotelID, nd.PackageID, nd.StartDate, nd.EndDate, Cost = SUM( hp.HotelPrice )
FROM (  SELECT  h.HotelID, p.PackageID, sp.StartDate, sp.EndDate,
NightDate = DATEADD( DAY, ROW_NUMBER() OVER ( ORDER BY dd.DetailsDurationID ) - 1, sp.StartDate )
FROM    dbo.StartPackage sp
INNER JOIN dbo.Package p
ON  sp.PackageID = p.PackageID
INNER JOIN dbo.PackageDuration pd
ON  pd.PackageID = p.PackageID
AND pd.NightCounts = DATEDIFF( DAY, sp.StartDate, sp.EndDate )
INNER JOIN dbo.DurationDetails dd
ON  dd.PackageDurationsID = pd.PackageDurationsID
INNER JOIN dbo.DayDetails dayd
ON  dayd.DetailsDurationID = dd.DetailsDurationID
INNER JOIN dbo.Hotel h
ON  h.HotelID = dayd.HotelID ) nd
INNER JOIN dbo.HotelPrice hp
ON  nd.HotelID = hp.HotelID
AND nd.NightDate >= hp.FromDate
AND nd.NightDate <= hp.ToDate
GROUP BY nd.HotelID, nd.PackageID, nd.StartDate, nd.EndDate ) hc
INNER JOIN dbo.Hotel h
ON  hc.HotelID = h.HotelID
INNER JOIN dbo.Package p
ON  hc.PackageID = p.PackageID
INNER JOIN dbo.TransferType ath
ON  ath.TransferType = 'from airport to hotel'
INNER JOIN dbo.TransferPeriod athc
ON  ath.TransferID = athc.TransferTypeID
AND hc.StartDate >= athc.FromDate
AND hc.StartDate <= athc.Todate
INNER JOIN dbo.TransferType htp
ON  htp.TransferType = 'From Hotel to parking'
INNER JOIN dbo.TransferPeriod htpc
ON  htp.TransferID = htpc.TransferTypeID
AND hc.EndDate >= htpc.FromDate
AND hc.EndDate <= htpc.Todate;
``````