如何获得每家酒店的总成本和每8天的换乘费用？

Question

问题

如何获得每家酒店的总成本和每8天的换乘费用？

详细信息

8天=7夜

意为8天=每家酒店7天的住宿

因为他最后一天乘飞机不是住在酒店。

结果我需要得到它

为什么在图像中显示为null，我需要像上面那样生成一行

这个图像屏幕拍摄错误

我使用以下查询

;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

样本数据库和数据

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

更新我在answer1中运行的查询，它只在第一行和两行额外的正确数据中给出了正确的结果

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

只有以上记录，但另一个数据是额外的，不正确。

Answer 1

想得太多了。查询中唯一有趣的部分应该是爆炸日期，以正确引用酒店房间的可变成本。

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;

如果你不需要在每家酒店：

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;