0

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 enter image description here

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

this image screen shoot of wrong enter image description here

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

query after run result

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.