How to get total cost of start package table between two periods by sql query?

Question

How to get total cost of start package table between two periods by sql query?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
July 14, 2017 at 5:27 pm

#324522

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    DAY8PackageDurationID 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      01DayDetailID 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 HotelName01   Hilton02   MovenpickHotelID HotelName
01   Hilton
02   Movenpick
HotelPrice table:
HotelPriceID FromDate ToDate HotelPrice HotelIDHP01    01/01/2017 30/06/2017 20    01HP02    01/07/2017 31/12/2017 30    01HP03    01/01/2017 30/06/2017 30    02HP04    01/07/2017 31/12/2017 40    02HotelPriceID 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 TotalCostSD01   p2   28/06/2017 05/07/2017 250StartID PackageID StartDate EndDate TotalCost
SD01   p2   28/06/2017 05/07/2017 250
Calculate cost for total cost column:
date    cost28/06/2017 20 29/06/2017 2030/06/2017 2001/07/2017 4002/07/2017 4003/07/2017 4004/07/2017 4005/07/2017 30 totalpackage 250 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
Database script
USE [NileTravel3]GO/****** Object: Table [dbo].[DayDetails] Script Date: 14/07/2017 11:16:59 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[View_1]ASSELECT   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.HotelPriceFROM    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.HotelIDGOINSERT [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])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]GOALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]GOALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])GOALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]GOALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]GOALTER TABLE [dbo].[PackageDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]GOALTER TABLE [dbo].[StartPackage] WITH CHECK ADD CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]GOUSE [NileTravel3]GO/****** Object: Table [dbo].[DayDetails] Script Date: 14/07/2017 11:16:59 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[View_1]ASSELECT   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.HotelPriceFROM    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.HotelIDGOINSERT [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])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]GOALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]GOALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])GOALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]GOALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]GOALTER TABLE [dbo].[PackageDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]GOALTER TABLE [dbo].[StartPackage] WITH CHECK ADD CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]GO
[/code]Database script

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

ahmed_elbarbary.2010 - Friday, July 14, 2017 5:27 PM
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    DAY8PackageDurationID 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      01DayDetailID 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 HotelName01   Hilton02   MovenpickHotelID HotelName
01   Hilton
02   Movenpick
HotelPrice table:
HotelPriceID FromDate ToDate HotelPrice HotelIDHP01    01/01/2017 30/06/2017 20    01HP02    01/07/2017 31/12/2017 30    01HP03    01/01/2017 30/06/2017 30    02HP04    01/07/2017 31/12/2017 40    02HotelPriceID 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 TotalCostSD01   p2   28/06/2017 05/07/2017 250StartID PackageID StartDate EndDate TotalCost
SD01   p2   28/06/2017 05/07/2017 250
Calculate cost for total cost column:
date    cost28/06/2017 20 29/06/2017 2030/06/2017 2001/07/2017 4002/07/2017 4003/07/2017 4004/07/2017 4005/07/2017 30 totalpackage 250 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
Database script
USE [NileTravel3]GO/****** Object: Table [dbo].[DayDetails] Script Date: 14/07/2017 11:16:59 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[View_1]ASSELECT   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.HotelPriceFROM    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.HotelIDGOINSERT [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])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]GOALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]GOALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])GOALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]GOALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]GOALTER TABLE [dbo].[PackageDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]GOALTER TABLE [dbo].[StartPackage] WITH CHECK ADD CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]GOUSE [NileTravel3]GO/****** Object: Table [dbo].[DayDetails] Script Date: 14/07/2017 11:16:59 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[View_1]ASSELECT   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.HotelPriceFROM    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.HotelIDGOINSERT [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])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]GOALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]GOALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])GOALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]GOALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])REFERENCES [dbo].[Hotel] ([HotelID])GOALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]GOALTER TABLE [dbo].[PackageDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]GOALTER TABLE [dbo].[StartPackage] WITH CHECK ADD CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])REFERENCES [dbo].[Package] ([PackageID])GOALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]GO
[/code]Database script

please check your setup scripts for errors / test it yourself and repost....as it stands at the moment there are far too many errors in it

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

full script for database
Im sorry for errors
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 EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[42] 4[1] 2[17] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Package" Begin Extent = Top = 6 Left = 38 Bottom = 118 Right = 208 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PackageDuration" Begin Extent = Top = 6 Left = 246 Bottom = 135 Right = 441 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "DurationDetails" Begin Extent = Top = 0 Left = 469 Bottom = 112 Right = 664 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "DayDetails" Begin Extent = Top = 120 Left = 38 Bottom = 232 Right = 219 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Hotel" Begin Extent = Top = 138 Left = 257 Bottom = 250 Right = 427 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "HotelPrice" Begin Extent = Top = 118 Left = 478 Bottom = 247 Right = 648 End DisplayFlags = 280 TopColumn = 1 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 12 Width = 284 Width = 1500 W' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'idth = 1080 Width = 1500 Width = 1500 Width = 780 Width = 1290 Width = 1275 Width = 1500 Width = 1155 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 570 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

thanks for updated script...it works🙂

based on your sample data...what results are you expecting, please?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 4

Thank you for reply
in start package table
I need to write sql query calculate hotel costs between two periods per package and the final result must show in total cost field
StartID PackageID StartDate EndDate TotalCost SD01 p2 28/06/2017 05/07/2017 250
the way to calculate the costs as following :
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
so that How to write sql query get total cost for hotels between two dates based on hotel price periods ?
How to get 250 as total cost by sql query

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 5

i do it as following and it working :
DECLARE @DateFrom DATE = '20170628';DECLARE @DateTo DATE = '20170705'; DECLARE @StartID NVARCHAR(255) = 'SD01'; WITH DurationDetailsNumeric AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY DD.PackageDurationsID ORDER BY DD.Days ) AS RN FROM DurationDetails DD ),[Data] AS ( SELECT SP.StartID ,P.PackageID ,SP.TotalCost ,SUM(HP.HotelPrice) AS TotalCostCalculated FROM StartPackage SP INNER JOIN Package P ON P.PackageID = SP.PackageID INNER JOIN PackageDuration PD ON PD.PackageID = P.PackageID INNER JOIN DurationDetailsNumeric DDN ON DDN.PackageDurationsID = PD.PackageDurationsID INNER JOIN DayDetails DyD ON DyD.DetailsDurationID = DDN.DetailsDurationID INNER JOIN Hotel H ON H.HotelID = DyD.HotelID INNER JOIN HotelPrice HP ON HP.HotelID = H.HotelID WHERE DATEADD(DAY, DDN.RN - 1, SP.StartDate) BETWEEN HP.FromDate AND HP.ToDate AND SP.StartID = @StartID AND DATEADD(DAY, DDN.RN - 1, SP.StartDate) BETWEEN @DateFrom AND @DateTo GROUP BY SP.StartID , P.PackageID , SP.TotalCost ) UPDATE SP SET SP.TotalCost = D.TotalCostCalculated FROM StartPackage SP INNER JOIN [Data] D ON D.StartID = SP.StartID; SELECT * FROM StartPackage SP;

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 6

but remaining to get packages related to hotels
can any one help me how to write sql query get packages related to hotel as below
i do as following

SELECT dbo.Hotel.HotelID, dbo.Hotel.HotelName, dbo.DurationDetails.Days, dbo.PackageDuration.PackageDuration, dbo.Package.PackageName, dbo.Package.Duration, dbo.StartPackage.StartDate, dbo.StartPackage.EndDateFROM dbo.Hotel INNER JOIN dbo.DayDetails ON dbo.Hotel.HotelID = dbo.DayDetails.HotelID INNER JOIN dbo.DurationDetails ON dbo.DayDetails.DetailsDurationID = dbo.DurationDetails.DetailsDurationID INNER JOIN dbo.PackageDuration ON dbo.DurationDetails.PackageDurationsID = dbo.PackageDuration.PackageDurationsID INNER JOIN dbo.Package ON dbo.PackageDuration.PackageID = dbo.Package.PackageID INNER JOIN dbo.StartPackage ON dbo.Package.PackageID = dbo.StartPackage.PackageID

1HiltonDAY18AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY28AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY38AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY48AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY58AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY68AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY78AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000 1HiltonDAY88AlexaPackage8,152017-06-28 00:00:00.0002017-07-05 00:00:00.000

but it give me details
i need summarizinf for it

HotelID HotelName package startdate end date 1 Hilton AlexaPackage 28-06-2017 05-07-2017