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
Code: Select all
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
Code: Select all
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