How to make relation between tables to calculate cost of flight and transfer and hotel and excursion per every day ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 6:56 am

  #331668

  I need to design database for Tourism Company organize more tours based on the following tables
  Hotels
  HotelID            Hotel Name
  11                                                      Basma Hotel
  22                                                      Hilton   Hotel
  33                                                      Movenpick Hotel
  Hotel price
  Hotel                             from date           to date                   price
  Basma Hotel                 01/05/2017    01/07/2017          500
  Basma Hotel                 02/07/2017    01/10/2017         600

  Package
      PachageName   Duration
     Basma Hotel       3,5 days
     Hilton Hotel       3 days
    Alexa                   3, 5, 7 days
  Alexa not hotel  but it is fixed tour meaning  may be inclue more excursion and more hotels
  PackageComponents
  PackagenName    PackageDuration
  Basma Hotel          3
  Basma Hotel         5
  Hilton Hotel          3
  Alexa                     3
  Alexa                    5
  Alexa                   7
  PackageComponentDetails for basma hotel 3 days as example
  PackageName       days   Duration  accommodation
  Basma Hotel        day1     3                  BasmaHotel                      
  Basma Hotel        day2     3                 BasmaHotel           
  Basma Hotel        day3    3                 BasmaHotel  
  And also 5 days as above but from day1 to day5       
  PackageComponentDetails costs  for basma hotel 3 days as example
  PackageName       days   Duration  accommodationcosts
  Basma Hotel        day1     3                  30                     
  Basma Hotel        day2     3                 30          
  Basma Hotel        day3    3                 30

  PackageComponentDetails for alexa
  PackageName              days    Duration        accommodation
  Basma Hotel                 day1       3                 BasmaHotel
  Hilton Hotel                  day2        3               Hilton Hotel    
  Movenpick Hotel        day3         3              Movenpick Hotel
  PackageComponentDetails costs for alexa
  PackageName                            days        Duration                accommodation costs
  Basma Hotel                                  day1              3                                        30
  Hilton Hotel                                   day2                3                                      50       
  Movenpick Hotel                day3                  3                                     20

  How to make relation between tables above to calculate cost per every day based on program to flight and hotels and excursion and transfer?

             

• Thom A

  SSC Guru

  Points: 99143

  More actions

  July 5, 2017 at 7:07 am

  #1949459

  You've been asking these questions in 3 separate topics so far already:
  https://www.sqlservercentral.com/Forums/1883089/How-to-calculate-cost-of-flight-and-hotel-and-transfer-per-every-program
  https://www.sqlservercentral.com/Forums/1883528/How-to-add-cost-per-hotel-and-flight-in-duration-cost-table
  https://www.sqlservercentral.com/Forums/1882891/How-to-calculate-cost-per-every-daycost-hotel-cost-flight-depending-on-flight-date
  All of these ask very similar questions. Is this homework?

  What have you tried so far, where is your DDL, DLM and expected results?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 7:16 am

  #1949463

  thank you for reply
  this is my diagram
  

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 7:23 am

  #1949466

  this is my DDL
  USE [NileTravel]
GO
/****** Object: Table [dbo].[DayDetails]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DayDetails](
    [DayDetailsID] [int] NOT NULL,
    [DetailsDurationID] [int] NULL,
    [FlightTypeID] [int] NULL,
    [HotelID] [int] NULL,
    [ExcursionID] [int] NULL,
    [TransferTypeID] [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].[DaysDetailsCost]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DaysDetailsCost](
    [DayCostID] [int] NOT NULL,
    [DateDuration] [datetime] NULL,
    [DetailsDurationID] [int] NULL,
    [FlightCost] [numeric](18, 0) NULL,
    [HotelCost] [numeric](18, 0) NULL,
    [ExcursionCost] [numeric](18, 0) NULL,
    [TransferCost] [numeric](18, 0) NULL,
CONSTRAINT [PK_DurationDetailsCost] PRIMARY KEY CLUSTERED
(
    [DayCostID] 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: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
    [DetailsDurationID] [int] NOT NULL,
    [ProgramDurationsID] [int] NULL,
    [Days] [int] 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].[Excursion]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Excursion](
    [ExcursionID] [int] NOT NULL,
    [ExcursionName] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Excursion] PRIMARY KEY CLUSTERED
(
    [ExcursionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[ExcursionPeriod]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExcursionPeriod](
    [ExcursionPeriodID] [int] NOT NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [ExcursionID] [int] NULL,
    [ExcursionPrice] [decimal](18, 0) NULL,
    [TotalExcursion] [decimal](18, 0) NULL,
CONSTRAINT [PK_ExcursionPeriod] PRIMARY KEY CLUSTERED
(
    [ExcursionPeriodID] 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].[FlightData]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightData](
    [FlighID] [nvarchar](50) NOT NULL,
    [FlightNo] [nvarchar](50) NOT NULL,
    [FlightDate] [datetime] NULL,
    [FlightTypeID] [int] NULL,
    [Arrival] [time](7) NULL,
    [Departure] [time](7) NULL,
    [AdultPrice] [money] NULL,
    [ChildPrice] [money] NULL,
    [Stock] [int] NULL,
    [TotalPrice] [decimal](18, 0) NULL,
    [Active] [bit] NULL,
CONSTRAINT [PK_FlightData_1] PRIMARY KEY CLUSTERED
(
    [FlighID] 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].[FlightRoute]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightRoute](
    [FlightTypeID] [int] NOT NULL,
    [FlightFrom] [nvarchar](max) NULL,
    [FlightTo] [nvarchar](max) NULL,
    [Active] [bit] NULL,
    [FlightRouteWay] [nvarchar](max) NULL,
CONSTRAINT [PK_FlightType] PRIMARY KEY CLUSTERED
(
    [FlightTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[Hotel]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotel](
    [HotelID] [int] NOT NULL,
    [HotelName] [nvarchar](50) NULL,
    [TypeID] [int] NULL,
    [Rating] [nvarchar](10) NULL,
    [DescriptionHotel] [text] 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] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[HotelPrice]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
    [HotelPriceID] [int] NOT NULL,
    [HotelID] [int] NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [HotelPrice] [decimal](18, 0) 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].[Program]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Program](
    [ProgramID] [int] NOT NULL,
    [ProgramName] [nvarchar](100) NULL,
    [Duration] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
    [IncludedItem] [nvarchar](max) NULL,
    [MetaTage] [text] NULL,
    [Title] [text] NULL,
    [HotelID] [int] NULL,
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
    [ProgramID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[ProgramDuration]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProgramDuration](
    [ProgramDurationsID] [int] NOT NULL,
    [ProgramID] [int] NULL,
    [ProgramDuration] [int] NULL,
    [NightCounts] [int] NULL,
CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED
(
    [ProgramDurationsID] 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].[RoomOccupation]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomOccupation](
    [RoomOccupationID] [int] NOT NULL,
    [OccupationNo] [int] NULL,
    [MaxAdult] [int] NULL,
    [MaxOccupation] [int] NULL,
    [OccupationPrice] [money] NULL,
    [HotelPriceID] [int] NULL,
    [RoomTypeID] [int] NULL,
CONSTRAINT [PK_RoomOccupation] PRIMARY KEY CLUSTERED
(
    [RoomOccupationID] 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].[RoomStock]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomStock](
    [RoomStockID] [int] NOT NULL,
    [RoomTypeID] [int] NULL,
    [Stock] [int] NULL,
    [RoomStockPrice] [numeric](18, 0) NULL,
CONSTRAINT [PK_RoomStock] PRIMARY KEY CLUSTERED
(
    [RoomStockID] 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].[RoomType]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomType](
    [RoomTypeID] [int] NOT NULL,
    [RoomType] [nvarchar](50) NULL,
CONSTRAINT [PK_RoomType] PRIMARY KEY CLUSTERED
(
    [RoomTypeID] 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: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferPeriod](
    [TransferID] [int] NOT NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [TransferTypeID] [int] NULL,
    [Price] [numeric](18, 0) NULL,
    [Total] [numeric](18, 0) NULL,
CONSTRAINT [PK_TransferData] 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
/****** Object: Table [dbo].[TransferType]  Script Date: 05/07/2017 3:20:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferType](
    [TransferTypeID] [int] NOT NULL,
    [TransferType] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferType] PRIMARY KEY CLUSTERED
(
    [TransferTypeID] 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
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_Excursion] FOREIGN KEY([ExcursionID])
REFERENCES [dbo].[Excursion] ([ExcursionID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Excursion]
GO
ALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_FlightRoute] FOREIGN KEY([FlightTypeID])
REFERENCES [dbo].[FlightRoute] ([FlightTypeID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_FlightRoute]
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] ([TransferTypeID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_TransferType]
GO
ALTER TABLE [dbo].[DaysDetailsCost] WITH CHECK ADD CONSTRAINT [FK_DurationDetailsCost_TourDuration] FOREIGN KEY([DetailsDurationID])
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])
GO
ALTER TABLE [dbo].[DaysDetailsCost] CHECK CONSTRAINT [FK_DurationDetailsCost_TourDuration]
GO
ALTER TABLE [dbo].[ExcursionPeriod] WITH CHECK ADD CONSTRAINT [FK_ExcursionPeriod_Excursion] FOREIGN KEY([ExcursionID])
REFERENCES [dbo].[Excursion] ([ExcursionID])
GO
ALTER TABLE [dbo].[ExcursionPeriod] CHECK CONSTRAINT [FK_ExcursionPeriod_Excursion]
GO
ALTER TABLE [dbo].[FlightData] WITH CHECK ADD CONSTRAINT [FK_FlightData_FlightData] FOREIGN KEY([FlighID])
REFERENCES [dbo].[FlightData] ([FlighID])
GO
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_FlightData]
GO
ALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_ProductPrice_Product]
GO
ALTER TABLE [dbo].[Program] WITH CHECK ADD CONSTRAINT [FK_Program_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[Program] CHECK CONSTRAINT [FK_Program_Hotel]
GO
ALTER TABLE [dbo].[ProgramDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([ProgramID])
REFERENCES [dbo].[Program] ([ProgramID])
GO
ALTER TABLE [dbo].[ProgramDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
GO
ALTER TABLE [dbo].[RoomOccupation] WITH CHECK ADD CONSTRAINT [FK_RoomOccupation_Product] FOREIGN KEY([HotelPriceID])
REFERENCES [dbo].[HotelPrice] ([HotelPriceID])
GO
ALTER TABLE [dbo].[RoomOccupation] CHECK CONSTRAINT [FK_RoomOccupation_Product]
GO
ALTER TABLE [dbo].[RoomOccupation] WITH CHECK ADD CONSTRAINT [FK_RoomOccupation_RoomType] FOREIGN KEY([RoomTypeID])
REFERENCES [dbo].[RoomType] ([RoomTypeID])
GO
ALTER TABLE [dbo].[RoomOccupation] CHECK CONSTRAINT [FK_RoomOccupation_RoomType]
GO
ALTER TABLE [dbo].[RoomStock] WITH CHECK ADD CONSTRAINT [FK_RoomStock_RoomStock] FOREIGN KEY([RoomTypeID])
REFERENCES [dbo].[RoomType] ([RoomTypeID])
GO
ALTER TABLE [dbo].[RoomStock] CHECK CONSTRAINT [FK_RoomStock_RoomStock]
GO
ALTER TABLE [dbo].[TransferPeriod] WITH CHECK ADD CONSTRAINT [FK_TransferData_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferTypeID])
GO
ALTER TABLE [dbo].[TransferPeriod] CHECK CONSTRAINT [FK_TransferData_TransferType]
GO

• Steve Jones - SSC Editor

  SSC Guru

  Points: 739397

  More actions

  July 5, 2017 at 8:39 am

  #1949491

  This doesn't show what you've tried. Put data in the tables, post the inserts, and post some queries that retrieve data.

  We are here to help, but not do the work for you.

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 11:59 am

  #1949549

  data as following
  USE [NileTravel]
GO
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (1, N'NileCruize')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (2, N'Sun')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (3, N'Sea')
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (1, N'alexa', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (2, N'sfinx', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (3, N'amon', 2)
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Basma', N'**')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'Movenpick', N'***')
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (1, N'HiltonPackage', N'4', 1, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (2, N'BasmaPackage', N'4,6', 2, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (3, N'alexaPackage', N'4', NULL, 1)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (4, N'sfinxPackage', N'4,6', NULL, 1)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (1, 1, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (2, 2, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (3, 2, 6, 5)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (4, 3, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (5, 4, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (6, 4, 6, 5)
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (1, 1, N'day1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (2, 1, N'day2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (3, 1, N'day3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (4, 1, N'day4')
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (1, CAST(0x0000A7BC00000000 AS DateTime), 1, CAST(100 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (2, CAST(0x0000A7BD00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (3, CAST(0x0000A7BE00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (4, CAST(0x0000A7BF00000000 AS DateTime), 1, CAST(120 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (1, N'Visit luxor musiem')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (2, N'Visit pyramides')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (3, N'Visit abo elhol')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (1, N'Amsterdam', N'Luxor', N'Amsterdam to luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (2, N'Luxor', N'Amsterdam', N'Luxor to amsterdam')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (3, N'Aswan', N'Luxor', N'Aswan to Luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (4, N'luxor', N'aswan', N'aswan to luxor')
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (1, N'From Hotel to airbort')
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (2, N'From Hotel to Musuem')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (1, 1, 1, 1, 1, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (2, 1, NULL, 1, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (3, 1, NULL, 1, NULL, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (4, 1, 2, 1, NULL, NULL)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (1, 1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A76A00000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (2, 1, CAST(0x0000A78900000000 AS DateTime), CAST(0x0000A7C900000000 AS DateTime), CAST(300 AS Decimal(18, 0)))
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A72900000000 AS DateTime), 1, CAST(50 AS Decimal(18, 0)), CAST(50 AS Decimal(18, 0)))
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (2, CAST(0x0000A72A00000000 AS DateTime), CAST(0x0000A7C300000000 AS DateTime), 1, CAST(60 AS Decimal(18, 0)), CAST(60 AS Decimal(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A74B00000000 AS DateTime), 1, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (2, CAST(0x0000A76600000000 AS DateTime), CAST(0x0000A7FF00000000 AS DateTime), 1, CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (3, CAST(0x0000A78500000000 AS DateTime), CAST(0x0000A7C200000000 AS DateTime), 2, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (1, N'Standard')
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (2, N'Family')
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (1, 1, 10, CAST(50 AS Numeric(18, 0)), 1)
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (2, 2, 10, CAST(60 AS Numeric(18, 0)), 2)
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'1', N'ms750', CAST(0x0000A7A300000000 AS DateTime), 1, N'06', N'10', 50.0000, 50.0000, 10, CAST(100 AS Decimal(18, 0)), 1)
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'2', N'ms800', CAST(0x0000A7C300000000 AS DateTime), 2, N'10', N'12', 60.0000, 60.0000, 20, CAST(120 AS Decimal(18, 0)), 1)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 5, 2017 at 12:05 pm

  #1949551

  ahmed_elbarbary.2010 - Wednesday, July 5, 2017 11:59 AM

  data as following
  USE [NileTravel]
GO
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (1, N'NileCruize')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (2, N'Sun')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (3, N'Sea')
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (1, N'alexa', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (2, N'sfinx', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (3, N'amon', 2)
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Basma', N'**')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'Movenpick', N'***')
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (1, N'HiltonPackage', N'4', 1, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (2, N'BasmaPackage', N'4,6', 2, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (3, N'alexaPackage', N'4', NULL, 1)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (4, N'sfinxPackage', N'4,6', NULL, 1)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (1, 1, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (2, 2, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (3, 2, 6, 5)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (4, 3, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (5, 4, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (6, 4, 6, 5)
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (1, 1, N'day1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (2, 1, N'day2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (3, 1, N'day3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (4, 1, N'day4')
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (1, CAST(0x0000A7BC00000000 AS DateTime), 1, CAST(100 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (2, CAST(0x0000A7BD00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (3, CAST(0x0000A7BE00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (4, CAST(0x0000A7BF00000000 AS DateTime), 1, CAST(120 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (1, N'Visit luxor musiem')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (2, N'Visit pyramides')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (3, N'Visit abo elhol')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (1, N'Amsterdam', N'Luxor', N'Amsterdam to luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (2, N'Luxor', N'Amsterdam', N'Luxor to amsterdam')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (3, N'Aswan', N'Luxor', N'Aswan to Luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (4, N'luxor', N'aswan', N'aswan to luxor')
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (1, N'From Hotel to airbort')
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (2, N'From Hotel to Musuem')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (1, 1, 1, 1, 1, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (2, 1, NULL, 1, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (3, 1, NULL, 1, NULL, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (4, 1, 2, 1, NULL, NULL)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (1, 1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A76A00000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (2, 1, CAST(0x0000A78900000000 AS DateTime), CAST(0x0000A7C900000000 AS DateTime), CAST(300 AS Decimal(18, 0)))
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A72900000000 AS DateTime), 1, CAST(50 AS Decimal(18, 0)), CAST(50 AS Decimal(18, 0)))
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (2, CAST(0x0000A72A00000000 AS DateTime), CAST(0x0000A7C300000000 AS DateTime), 1, CAST(60 AS Decimal(18, 0)), CAST(60 AS Decimal(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A74B00000000 AS DateTime), 1, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (2, CAST(0x0000A76600000000 AS DateTime), CAST(0x0000A7FF00000000 AS DateTime), 1, CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (3, CAST(0x0000A78500000000 AS DateTime), CAST(0x0000A7C200000000 AS DateTime), 2, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (1, N'Standard')
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (2, N'Family')
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (1, 1, 10, CAST(50 AS Numeric(18, 0)), 1)
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (2, 2, 10, CAST(60 AS Numeric(18, 0)), 2)
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'1', N'ms750', CAST(0x0000A7A300000000 AS DateTime), 1, N'06', N'10', 50.0000, 50.0000, 10, CAST(100 AS Decimal(18, 0)), 1)
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'2', N'ms800', CAST(0x0000A7C300000000 AS DateTime), 2, N'10', N'12', 60.0000, 60.0000, 20, CAST(120 AS Decimal(18, 0)), 1)

  sorry...this doesnt work with your previous code...please repost entire script for creation/insert ...after you have fully tested that they work.

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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 739397

  More actions

  July 5, 2017 at 12:39 pm

  #1949554

  And post your attempt. Again, we are not here to do your work.

  Everyone, please  DO NOT post a solution if there is no attempt by the OP.

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 1:10 pm

  #1949560

  USE [NileTravel]
GO
/****** Object: Table [dbo].[DayDetails]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DayDetails](
    [DayDetailsID] [int] NOT NULL,
    [DetailsDurationID] [int] NULL,
    [FlightTypeID] [int] NULL,
    [HotelID] [int] NULL,
    [ExcursionID] [int] NULL,
    [TransferTypeID] [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].[DaysDetailsCost]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DaysDetailsCost](
    [DayCostID] [int] NOT NULL,
    [DateDuration] [datetime] NULL,
    [DetailsDurationID] [int] NULL,
    [FlightCost] [numeric](18, 0) NULL,
    [HotelCost] [numeric](18, 0) NULL,
    [ExcursionCost] [numeric](18, 0) NULL,
    [TransferCost] [numeric](18, 0) NULL,
CONSTRAINT [PK_DurationDetailsCost] PRIMARY KEY CLUSTERED
(
    [DayCostID] 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: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
    [DetailsDurationID] [int] NOT NULL,
    [PackageDurationsID] [int] 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].[Excursion]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Excursion](
    [ExcursionID] [int] NOT NULL,
    [ExcursionName] [nvarchar](50) NULL,
CONSTRAINT [PK_Excursion] PRIMARY KEY CLUSTERED
(
    [ExcursionID] 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].[ExcursionPeriod]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExcursionPeriod](
    [ExcursionPeriodID] [int] NOT NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [ExcursionID] [int] NULL,
    [ExcursionPrice] [decimal](18, 0) NULL,
    [TotalExcursion] [decimal](18, 0) NULL,
CONSTRAINT [PK_ExcursionPeriod] PRIMARY KEY CLUSTERED
(
    [ExcursionPeriodID] 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].[FlightData]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightData](
    [FlighID] [nvarchar](50) NOT NULL,
    [FlightNo] [nvarchar](50) NOT NULL,
    [FlightDate] [datetime] NULL,
    [FlightTypeID] [int] NULL,
    [Arrival] [nvarchar](50) NULL,
    [Departure] [nvarchar](50) NULL,
    [AdultPrice] [money] NULL,
    [ChildPrice] [money] NULL,
    [Stock] [int] NULL,
    [TotalPrice] [decimal](18, 0) NULL,
    [Active] [bit] NULL,
CONSTRAINT [PK_FlightData_1] PRIMARY KEY CLUSTERED
(
    [FlighID] 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].[FlightRoute]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightRoute](
    [FlightTypeID] [int] NOT NULL,
    [FlightFrom] [nvarchar](max) NULL,
    [FlightTo] [nvarchar](max) NULL,
    [FlightRouteWay] [nvarchar](max) NULL,
CONSTRAINT [PK_FlightType] PRIMARY KEY CLUSTERED
(
    [FlightTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
/****** Object: Table [dbo].[Hotel]  Script Date: 05/07/2017 9:08:12 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: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
    [HotelPriceID] [int] NOT NULL,
    [HotelID] [int] NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [HotelPrice] [decimal](18, 0) 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: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Package](
    [PackageID] [int] NOT NULL,
    [PackageName] [nvarchar](100) NULL,
    [Duration] [nvarchar](50) NULL,
    [HotelID] [int] NULL,
    [ProgramID] [int] 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: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackageDuration](
    [PackageDurationsID] [int] NOT NULL,
    [PackageID] [int] 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].[Program]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Program](
    [ProgramID] [int] NOT NULL,
    [ProgramName] [nvarchar](50) NULL,
    [ProgramTypeID] [int] NULL,
CONSTRAINT [PK_Program] PRIMARY KEY CLUSTERED
(
    [ProgramID] 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].[RoomStock]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomStock](
    [RoomStockID] [int] NOT NULL,
    [RoomTypeID] [int] NULL,
    [Stock] [int] NULL,
    [RoomStockPrice] [numeric](18, 0) NULL,
    [HotelPriceID] [int] NULL,
CONSTRAINT [PK_RoomStock] PRIMARY KEY CLUSTERED
(
    [RoomStockID] 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].[RoomType]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomType](
    [RoomTypeID] [int] NOT NULL,
    [RoomType] [nvarchar](50) NULL,
CONSTRAINT [PK_RoomType] PRIMARY KEY CLUSTERED
(
    [RoomTypeID] 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: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferPeriod](
    [TransferID] [int] NOT NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [TransferTypeID] [int] NULL,
    [Price] [numeric](18, 0) NULL,
    [Total] [numeric](18, 0) NULL,
CONSTRAINT [PK_TransferData] 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
/****** Object: Table [dbo].[TransferType]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferType](
    [TransferTypeID] [int] NOT NULL,
    [TransferType] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferType] PRIMARY KEY CLUSTERED
(
    [TransferTypeID] 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].[Type]  Script Date: 05/07/2017 9:08:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Type](
    [ProgramTypeID] [int] NOT NULL,
    [ProgramType] [nvarchar](50) NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED
(
    [ProgramTypeID] 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], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (1, 1, 1, 1, 1, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (2, 1, NULL, 1, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (3, 1, NULL, 1, NULL, 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [FlightTypeID], [HotelID], [ExcursionID], [TransferTypeID]) VALUES (4, 1, 2, 1, NULL, NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (1, CAST(0x0000A7BC00000000 AS DateTime), 1, CAST(100 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (2, CAST(0x0000A7BD00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (3, CAST(0x0000A7BE00000000 AS DateTime), 1, NULL, CAST(300 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)), NULL)
INSERT [dbo].[DaysDetailsCost] ([DayCostID], [DateDuration], [DetailsDurationID], [FlightCost], [HotelCost], [ExcursionCost], [TransferCost]) VALUES (4, CAST(0x0000A7BF00000000 AS DateTime), 1, CAST(120 AS Numeric(18, 0)), CAST(300 AS Numeric(18, 0)), NULL, NULL)
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (1, 1, N'day1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (2, 1, N'day2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (3, 1, N'day3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (4, 1, N'day4')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (1, N'Visit luxor musiem')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (2, N'Visit pyramides')
INSERT [dbo].[Excursion] ([ExcursionID], [ExcursionName]) VALUES (3, N'Visit abo elhol')
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A72900000000 AS DateTime), 1, CAST(50 AS Decimal(18, 0)), CAST(50 AS Decimal(18, 0)))
INSERT [dbo].[ExcursionPeriod] ([ExcursionPeriodID], [FromDate], [ToDate], [ExcursionID], [ExcursionPrice], [TotalExcursion]) VALUES (2, CAST(0x0000A72A00000000 AS DateTime), CAST(0x0000A7C300000000 AS DateTime), 1, CAST(60 AS Decimal(18, 0)), CAST(60 AS Decimal(18, 0)))
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'1', N'ms750', CAST(0x0000A7A300000000 AS DateTime), 1, N'06', N'10', 50.0000, 50.0000, 10, CAST(100 AS Decimal(18, 0)), 1)
INSERT [dbo].[FlightData] ([FlighID], [FlightNo], [FlightDate], [FlightTypeID], [Arrival], [Departure], [AdultPrice], [ChildPrice], [Stock], [TotalPrice], [Active]) VALUES (N'2', N'ms800', CAST(0x0000A7C300000000 AS DateTime), 2, N'10', N'12', 60.0000, 60.0000, 20, CAST(120 AS Decimal(18, 0)), 1)
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (1, N'Amsterdam', N'Luxor', N'Amsterdam to luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (2, N'Luxor', N'Amsterdam', N'Luxor to amsterdam')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (3, N'Aswan', N'Luxor', N'Aswan to Luxor')
INSERT [dbo].[FlightRoute] ([FlightTypeID], [FlightFrom], [FlightTo], [FlightRouteWay]) VALUES (4, N'luxor', N'aswan', N'aswan to luxor')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Basma', N'**')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'Movenpick', N'***')
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (1, 1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A76A00000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[HotelPrice] ([HotelPriceID], [HotelID], [FromDate], [ToDate], [HotelPrice]) VALUES (2, 1, CAST(0x0000A78900000000 AS DateTime), CAST(0x0000A7C900000000 AS DateTime), CAST(300 AS Decimal(18, 0)))
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (1, N'HiltonPackage', N'4', 1, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (2, N'BasmaPackage', N'4,6', 2, NULL)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (3, N'alexaPackage', N'4', NULL, 1)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [HotelID], [ProgramID]) VALUES (4, N'sfinxPackage', N'4,6', NULL, 1)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (1, 1, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (2, 2, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (3, 2, 6, 5)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (4, 3, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (5, 4, 4, 3)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (6, 4, 6, 5)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (1, N'alexa', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (2, N'sfinx', 1)
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (3, N'amon', 2)
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (1, 1, 10, CAST(50 AS Numeric(18, 0)), 1)
INSERT [dbo].[RoomStock] ([RoomStockID], [RoomTypeID], [Stock], [RoomStockPrice], [HotelPriceID]) VALUES (2, 2, 10, CAST(60 AS Numeric(18, 0)), 2)
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (1, N'Standard')
INSERT [dbo].[RoomType] ([RoomTypeID], [RoomType]) VALUES (2, N'Family')
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (1, CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A74B00000000 AS DateTime), 1, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (2, CAST(0x0000A76600000000 AS DateTime), CAST(0x0000A7FF00000000 AS DateTime), 1, CAST(60 AS Numeric(18, 0)), CAST(60 AS Numeric(18, 0)))
INSERT [dbo].[TransferPeriod] ([TransferID], [FromDate], [ToDate], [TransferTypeID], [Price], [Total]) VALUES (3, CAST(0x0000A78500000000 AS DateTime), CAST(0x0000A7C200000000 AS DateTime), 2, CAST(50 AS Numeric(18, 0)), CAST(50 AS Numeric(18, 0)))
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (1, N'From Hotel to airbort')
INSERT [dbo].[TransferType] ([TransferTypeID], [TransferType]) VALUES (2, N'From Hotel to Musuem')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (1, N'NileCruize')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (2, N'Sun')
INSERT [dbo].[Type] ([ProgramTypeID], [ProgramType]) VALUES (3, N'Sea')
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_Excursion] FOREIGN KEY([ExcursionID])
REFERENCES [dbo].[Excursion] ([ExcursionID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Excursion]
GO
ALTER TABLE [dbo].[DayDetails] WITH CHECK ADD CONSTRAINT [FK_DayDetails_FlightRoute] FOREIGN KEY([FlightTypeID])
REFERENCES [dbo].[FlightRoute] ([FlightTypeID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_FlightRoute]
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] ([TransferTypeID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_TransferType]
GO
ALTER TABLE [dbo].[DaysDetailsCost] WITH CHECK ADD CONSTRAINT [FK_DurationDetailsCost_TourDuration] FOREIGN KEY([DetailsDurationID])
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])
GO
ALTER TABLE [dbo].[DaysDetailsCost] CHECK CONSTRAINT [FK_DurationDetailsCost_TourDuration]
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].[ExcursionPeriod] WITH CHECK ADD CONSTRAINT [FK_ExcursionPeriod_Excursion] FOREIGN KEY([ExcursionID])
REFERENCES [dbo].[Excursion] ([ExcursionID])
GO
ALTER TABLE [dbo].[ExcursionPeriod] CHECK CONSTRAINT [FK_ExcursionPeriod_Excursion]
GO
ALTER TABLE [dbo].[FlightData] WITH CHECK ADD CONSTRAINT [FK_FlightData_FlightData] FOREIGN KEY([FlighID])
REFERENCES [dbo].[FlightData] ([FlighID])
GO
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_FlightData]
GO
ALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_ProductPrice_Product]
GO
ALTER TABLE [dbo].[Package] WITH CHECK ADD CONSTRAINT [FK_Program_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[Package] CHECK CONSTRAINT [FK_Program_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].[Program] WITH CHECK ADD CONSTRAINT [FK_Program_Type] FOREIGN KEY([ProgramTypeID])
REFERENCES [dbo].[Type] ([ProgramTypeID])
GO
ALTER TABLE [dbo].[Program] CHECK CONSTRAINT [FK_Program_Type]
GO
ALTER TABLE [dbo].[RoomStock] WITH CHECK ADD CONSTRAINT [FK_RoomStock_RoomStock] FOREIGN KEY([RoomTypeID])
REFERENCES [dbo].[RoomType] ([RoomTypeID])
GO
ALTER TABLE [dbo].[RoomStock] CHECK CONSTRAINT [FK_RoomStock_RoomStock]
GO
ALTER TABLE [dbo].[TransferPeriod] WITH CHECK ADD CONSTRAINT [FK_TransferData_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferTypeID])
GO
ALTER TABLE [dbo].[TransferPeriod] CHECK CONSTRAINT [FK_TransferData_TransferType]
GO

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 5, 2017 at 3:07 pm

  #1949573

  if you see this image it will explain every thing in my database
  and every thing i need to do it
  

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 6, 2017 at 7:46 am

  #1949715

  Thank you 
  I dont request from us do home work for me
  but i give you all details to help me
  but until now now any one can help so that i ask again

• Thom A

  SSC Guru

  Points: 99143

  More actions

  July 6, 2017 at 7:56 am

  #1949719

  ahmed_elbarbary.2010 - Thursday, July 6, 2017 7:46 AM

  Thank you 
  I dont request from us do home work for me
  but i give you all details to help me
  but until now now any one can help so that i ask again

  Could you supply what you've tried so far please?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 6, 2017 at 9:31 am

  #1949744

  to get details for day i write following :
  SELECT   dbo.Package.PackageID, dbo.Package.PackageName, dbo.Package.Duration, dbo.PackageDuration.PackageDuration, dbo.DurationDetails.Days,
         dbo.DayDetails.FlightTypeID, dbo.DayDetails.HotelID, dbo.DayDetails.ExcursionID, dbo.DayDetails.TransferTypeID
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
  Result
  1    HiltonPackage    4    4    day1    1    1    1    1
1    HiltonPackage    4    4    day1    NULL    1    NULL    NULL
1    HiltonPackage    4    4    day1    NULL    1    NULL    1
1    HiltonPackage    4    4    day1    2    1    NULL    NULL

  to get cost
  SELECT   dbo.Package.PackageID, dbo.Package.PackageName, dbo.Package.Duration, dbo.PackageDuration.PackageDuration, dbo.DurationDetails.Days,
         dbo.DayDetails.HotelID, dbo.DayDetails.ExcursionID, dbo.DayDetails.TransferTypeID, dbo.DaysDetailsCost.FlightCost, dbo.DaysDetailsCost.HotelCost,
         dbo.DaysDetailsCost.ExcursionCost, dbo.DaysDetailsCost.TransferCost
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.DaysDetailsCost ON dbo.DurationDetails.DetailsDurationID = dbo.DaysDetailsCost.DetailsDurationID
  result
  1    HiltonPackage    4    4    day1    1    1    1    100    300    60    60    2017-07-26 00:00:00.000
1    HiltonPackage    4    4    day1    1    1    1    NULL    300    NULL    NULL    2017-07-27 00:00:00.000
1    HiltonPackage    4    4    day1    1    1    1    NULL    300    60    NULL    2017-07-28 00:00:00.000
1    HiltonPackage    4    4    day1    1    1    1    120    300    NULL    NULL    2017-07-29 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    100    300    60    60    2017-07-26 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    NULL    300    NULL    NULL    2017-07-27 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    NULL    300    60    NULL    2017-07-28 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    120    300    NULL    NULL    2017-07-29 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    1    100    300    60    60    2017-07-26 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    1    NULL    300    NULL    NULL    2017-07-27 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    1    NULL    300    60    NULL    2017-07-28 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    1    120    300    NULL    NULL    2017-07-29 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    100    300    60    60    2017-07-26 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    NULL    300    NULL    NULL    2017-07-27 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    NULL    300    60    NULL    2017-07-28 00:00:00.000
1    HiltonPackage    4    4    day1    1    NULL    NULL    120    300    NULL    NULL    2017-07-29 00:00:00.000
  screen shoot image
  

• Thom A

  SSC Guru

  Points: 99143

  More actions

  July 6, 2017 at 9:38 am

  #1949748

  What have you attempt to answer your question? What have you tried to do to "calculate cost of flight and transfer and hotel and excursion".

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  July 6, 2017 at 9:44 am

  #1949753

  Can you help me to do calculate for flight and transfer and excursion and hotel
  or you can tell me any one from above and i will do others