How to get total cost of start package table between two per

Question

Problem: how to get total cost of start package table between two periods with a SQL query?

alexaPackage  from 28/06/2017 to 05/07/2017 8days  cost ?

Details

I need to get total cost in start package table between two dates, start date and end date. This cost between two periods represent cost of hotels found on every packages depend on period per every hotel price.

Image of diagram and view all data

 

Package table:

    PackageID   PackageName   Duration
        p1         sfinx         8
        p2         alexa         8,15
PackageDuration table:

PackageDurationID  PackageID  Duration NightCount
PD01                   p2        8        7
PD02                   p2        15       14

DurationDetails table:

    DetailDurationID   PackageDurationID   Days
    DD01                     PD01          DAY1
    DD02                     PD01          DAY2
    DD03                     PD01          DAY3
    DD04                     PD01          DAY4
    DD05                     PD01          DAY5
    DD06                     PD01          DAY6
    DD07                     PD01          DAY7
    DD08                     PD01          DAY8
DayDetails table:

DayDetailID  DetailDurationID  HotelID
DayD01        DD01                01
DayD02        DD02                01
DayD03        DD03                01
DayD04        DD04                02
DayD05        DD05                02
DayD06        DD06                02
DayD07        DD07                02
DayD08        DD08                01
Hotel table:

HotelID  HotelName
01        Hilton
02        Movenpick
HotelPrice table:

HotelPriceID    FromDate   ToDate     HotelPrice HotelID
HP01            01/01/2017 30/06/2017 20          01
HP02            01/07/2017 31/12/2017 30          01
HP03            01/01/2017 30/06/2017 30          02
HP04            01/07/2017 31/12/2017 40          02
StartPackage table:

StartID   PackageID  StartDate   EndDate     TotalCost
SD01       p2        28/06/2017  05/07/2017   250
Calculate cost for total cost column:

date          cost
28/06/2017     20
29/06/2017     20
30/06/2017     20
01/07/2017     40
02/07/2017     40
03/07/2017     40
04/07/2017     40
05/07/2017     30
totalpackage  250

USE [NileTravel3]
GO
/****** Object:  Table [dbo].[DayDetails]    Script Date: 14/07/2017 11:16:59 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,
 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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,
 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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:  View [dbo].[View_1]    Script Date: 14/07/2017 11:17:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT        dbo.Package.PackageName, dbo.Package.Duration, dbo.PackageDuration.PackageDuration, dbo.PackageDuration.NightCounts, dbo.DurationDetails.Days,
                         dbo.Hotel.HotelName, dbo.HotelPrice.FromDate, dbo.HotelPrice.ToDate, dbo.HotelPrice.HotelPrice
FROM            dbo.Package INNER JOIN
                         dbo.PackageDuration ON dbo.Package.PackageID = dbo.PackageDuration.PackageID INNER JOIN
                         dbo.DurationDetails ON dbo.PackageDuration.PackageDurationsID = dbo.DurationDetails.PackageDurationsID INNER JOIN
                         dbo.DayDetails ON dbo.DurationDetails.DetailsDurationID = dbo.DayDetails.DetailsDurationID INNER JOIN
                         dbo.Hotel ON dbo.DayDetails.HotelID = dbo.Hotel.HotelID INNER JOIN
                         dbo.HotelPrice ON dbo.Hotel.HotelID = dbo.HotelPrice.HotelID

GO
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD01', N'DD01', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD02', N'DD02', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD03', N'DD03', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD04', N'DD04', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD05', N'DD05', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD06', N'DD06', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD07', N'DD07', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD08', N'DD08', 1)
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].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Movenpick', 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].[Package] ([PackageID], [PackageName], [Duration]) VALUES (N'P02', N'AlexaPackage', N'8,15')
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].[StartPackage] ([StartID], [PackageID], [StartDate], [EndDate], [TotalCost]) VALUES (N'SD01', N'P02', CAST(0x0000A7A000000000 AS DateTime), CAST(0x0000A7A700000000 AS DateTime), CAST(250 AS Decimal(18, 0)))
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].[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