How to get cost of (flight + transfer + excursion + transfer

Question

How to get cost of (flight + transfer + excursion + transfer) per Tour ?

suppose i select flight date 25/06/2017 then select program alexia 5 days

How i select cost per every duration details

and put them in cost

my full database script as following

USE [NileTravel]
GO
/****** Object: Table [dbo].[ActivityType] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActivityType](
[ActivityTypeID] [int] NOT NULL,
[ActivityType] [nvarchar](50) NULL,
[ProductID] [int] NULL,
CONSTRAINT [PK_ActivityType] PRIMARY KEY CLUSTERED
(
[ActivityTypeID] 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].[AirLine] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AirLine](
[AirLineID] [int] NOT NULL,
[AirLineName] [nvarchar](50) NULL,
CONSTRAINT [PK_AirLine] PRIMARY KEY CLUSTERED
(
[AirLineID] 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].[ChildrenDiscount] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChildrenDiscount](
[ChildrenDiscountID] [int] NOT NULL,
[ProductID] [int] NULL,
[MinAge] [int] NULL,
[MaxAge] [int] NULL,
[Discount] [money] NULL,
CONSTRAINT [PK_ChildrenDiscount] PRIMARY KEY CLUSTERED
(
[ChildrenDiscountID] 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].[Destination] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Destination](
[BestimID] [int] NOT NULL,
[BestimName] [nvarchar](50) NULL,
[ParentID] [int] NULL,
[Image] [varbinary](max) NULL,
CONSTRAINT [PK_Bestiming] PRIMARY KEY CLUSTERED
(
[BestimID] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DurationDetails] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
[DetailsDurationID] [int] NOT NULL,
[PackageDurationsID] [int] NULL,
[Days] [int] NULL,
[FlightTypeID] [int] NULL,
[ProductID] [int] NULL,
[ExcursionID] [int] NULL,
[TransferTypeID] [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: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Excursion](
[ExcursionID] [int] NOT NULL,
[ExcursionName] [nvarchar](50) NULL,
[BestimID] [int] NULL,
[ExcursionTypeID] [int] 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: 26/06/2017 11:00:31 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] [numeric](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].[ExcursionType] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExcursionType](
[ExcursionTypeID] [int] NOT NULL,
[ExcursionType] [nvarchar](50) NULL,
CONSTRAINT [PK_ExcursionType] PRIMARY KEY CLUSTERED
(
[ExcursionTypeID] 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: 26/06/2017 11:00:31 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,
[AirLineID] [int] NULL,
[Arrival] [time](7) NULL,
[Departure] [time](7) NULL,
[AdultPrice] [money] NULL,
[ChildPrice] [money] NULL,
[Stock] [int] NULL,
[TotalPrice] [numeric](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: 26/06/2017 11:00:31 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,
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].[FlightWay] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlightWay](
[FlightWayID] [int] NOT NULL,
[FlightWay] [nvarchar](max) NULL,
CONSTRAINT [PK_FlightWay] PRIMARY KEY CLUSTERED
(
[FlightWayID] 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: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotel](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](50) NULL,
[BestimmingID] [int] NULL,
[TypeID] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] 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: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
[ProductPriceID] [nchar](10) NOT NULL,
[ProductID] [int] NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[HotelPrice] [numeric](18, 0) NULL,
CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED
(
[ProductPriceID] 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].[Luggage] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Luggage](
[LuggageID] [int] NOT NULL,
[AirLineID] [int] NULL,
[KG] [float] NULL,
[CostPrice] [int] NULL,
[SalesPrice] [int] NULL,
[LuggageName] [nvarchar](50) NULL,
CONSTRAINT [PK_Luggage] PRIMARY KEY CLUSTERED
(
[LuggageID] 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].[Order] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
[OrderNo] [nchar](10) NOT NULL,
[OrderDate] [datetime] NULL,
[UserID] [int] NULL,
[PackageID] [int] NULL,
[Price] [numeric](18, 0) NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[Period] [int] NULL,
[Invoice] [money] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderNo] 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].[PackageFlight] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackageFlight](
[PackageID] [int] NULL,
[FlightID] [int] NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Parent] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[ParentID] [int] NOT NULL,
[Subject] [nvarchar](50) NULL,
[Text] [nvarchar](max) NULL,
[Slug] [nvarchar](50) NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[ParentID] 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].[RoomOccupation] Script Date: 26/06/2017 11:00:31 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,
[ProductID] [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].[RoomService] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomService](
[RoomServiceID] [int] NOT NULL,
[RoomService] [nvarchar](50) NULL,
[ProductID] [int] NULL,
[ServiceTypeID] [int] NULL,
[ServicePrice] [numeric](18, 0) NULL,
CONSTRAINT [PK_RoomService] PRIMARY KEY CLUSTERED
(
[RoomServiceID] 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: 26/06/2017 11:00:31 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: 26/06/2017 11:00:31 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].[ServicesType] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServicesType](
[ServiceTypeID] [int] NOT NULL,
[ServiceType] [nvarchar](50) NULL,
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
[ServiceTypeID] 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].[Tour] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tour](
[PackageID] [int] NOT NULL,
[PackageName] [nvarchar](100) NULL,
[BestimID] [int] 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].[TourDestination] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourDestination](
[PackageID] [int] NOT NULL,
[BestomingID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TourDuration] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourDuration](
[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].[TourType] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TourType](
[PackageTypeID] [int] NOT NULL,
[PackageType] [nvarchar](50) NULL,
CONSTRAINT [PK_PackageType] PRIMARY KEY CLUSTERED
(
[PackageTypeID] 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: 26/06/2017 11:00:31 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,
[Margin] [nvarchar](50) 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: 26/06/2017 11:00:31 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].[Users] Script Date: 26/06/2017 11:00:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] NOT NULL,
[UserName] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Emails] [nvarchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] 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].[ActivityType] WITH CHECK ADD CONSTRAINT [FK_ActivityType_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[ActivityType] CHECK CONSTRAINT [FK_ActivityType_Product]
GO
ALTER TABLE [dbo].[ChildrenDiscount] WITH CHECK ADD CONSTRAINT [FK_ChildrenDiscount_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[ChildrenDiscount] CHECK CONSTRAINT [FK_ChildrenDiscount_Product]
GO
ALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_DurationDetails] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_DurationDetails]
GO
ALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_Excursion] FOREIGN KEY([ExcursionID])
REFERENCES [dbo].[Excursion] ([ExcursionID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_Excursion]
GO
ALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_FlightRoute] FOREIGN KEY([FlightTypeID])
REFERENCES [dbo].[FlightRoute] ([FlightTypeID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_FlightRoute]
GO
ALTER TABLE [dbo].[DurationDetails] WITH CHECK ADD CONSTRAINT [FK_DurationDetails_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferTypeID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_TransferType]
GO
ALTER TABLE [dbo].[Excursion] WITH CHECK ADD CONSTRAINT [FK_Excursion_Bestiming] FOREIGN KEY([BestimID])
REFERENCES [dbo].[Destination] ([BestimID])
GO
ALTER TABLE [dbo].[Excursion] CHECK CONSTRAINT [FK_Excursion_Bestiming]
GO
ALTER TABLE [dbo].[Excursion] WITH CHECK ADD CONSTRAINT [FK_Excursion_Excursion] FOREIGN KEY([ExcursionTypeID])
REFERENCES [dbo].[ExcursionType] ([ExcursionTypeID])
GO
ALTER TABLE [dbo].[Excursion] CHECK CONSTRAINT [FK_Excursion_Excursion]
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_AirLine] FOREIGN KEY([AirLineID])
REFERENCES [dbo].[AirLine] ([AirLineID])
GO
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_AirLine]
GO
ALTER TABLE [dbo].[FlightData] WITH CHECK ADD CONSTRAINT [FK_FlightData_FlightRoute] FOREIGN KEY([FlightTypeID])
REFERENCES [dbo].[FlightRoute] ([FlightTypeID])
GO
ALTER TABLE [dbo].[FlightData] CHECK CONSTRAINT [FK_FlightData_FlightRoute]
GO
ALTER TABLE [dbo].[HotelPrice] WITH CHECK ADD CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_ProductPrice_Product]
GO
ALTER TABLE [dbo].[Luggage] WITH CHECK ADD CONSTRAINT [FK_Luggage_AirLine] FOREIGN KEY([AirLineID])
REFERENCES [dbo].[AirLine] ([AirLineID])
GO
ALTER TABLE [dbo].[Luggage] CHECK CONSTRAINT [FK_Luggage_AirLine]
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Tour] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Tour] ([PackageID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Tour]
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Users]
GO
ALTER TABLE [dbo].[RoomOccupation] WITH CHECK ADD CONSTRAINT [FK_RoomOccupation_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
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].[RoomService] WITH CHECK ADD CONSTRAINT [FK_RoomService_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Hotel] ([ProductID])
GO
ALTER TABLE [dbo].[RoomService] CHECK CONSTRAINT [FK_RoomService_Product]
GO
ALTER TABLE [dbo].[RoomService] WITH CHECK ADD CONSTRAINT [FK_RoomService_ServicesType] FOREIGN KEY([ServiceTypeID])
REFERENCES [dbo].[ServicesType] ([ServiceTypeID])
GO
ALTER TABLE [dbo].[RoomService] CHECK CONSTRAINT [FK_RoomService_ServicesType]
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].[TourDestination] WITH CHECK ADD CONSTRAINT [FK_PackageBestim_Bestiming] FOREIGN KEY([BestomingID])
REFERENCES [dbo].[Destination] ([BestimID])
GO
ALTER TABLE [dbo].[TourDestination] CHECK CONSTRAINT [FK_PackageBestim_Bestiming]
GO
ALTER TABLE [dbo].[TourDestination] WITH CHECK ADD CONSTRAINT [FK_PackageBestim_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Tour] ([PackageID])
GO
ALTER TABLE [dbo].[TourDestination] CHECK CONSTRAINT [FK_PackageBestim_Package]
GO
ALTER TABLE [dbo].[TourDuration] WITH CHECK ADD CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Tour] ([PackageID])
GO
ALTER TABLE [dbo].[TourDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
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

suppose i select flight date 25/06/2017 then select program alexia 5 days

How i select cost per every duration details

and put them in cost

ahmed salah · Answer

can any one help me if possible

ahmed salah · Answer

1 Table - program + -----------+-------------+ 2 | ProgramID | ProgramName | 3 + -----------+-------------+ 4 | 1 | Alexia | 5 | 2 | Amon | 6 | 3 | Sfinx | 7 + -----------+-------------+ Table - ProgramDuration + ------------+-----------+---------------+ 2 | DurationNo | programID | Duration | 3 + ------------+-----------+---------------+ 4 | 1 | 1 | 8 for Alexia | 5 | 2 | 1 | 15 for Alexia | 6 + ------------+-----------+---------------+ Table Duration Details +------+--------+--------------------+-------------------+ | Days | Hotel | Flight | transfers | +------+--------+--------------------+-------------------+ | Day1 | Hilton | amsterdam to luxor | airport to hotel | | Day2 | Hilton | | AbuSimple musuem | | Day3 | Hilton | | | | Day4 | Hilton | | | | Day5 | Hilton | Luxor to amsterdam | | +------+--------+--------------------+-------------------+ +------------+-------+--------+----------+ | Date | Hotel | Flight | Transfer | +------------+-------+--------+----------+ | 25/06/2017 | 25 | 500 | 20 | | 26/06/2017 | 25 | | 55 | | 27/06/2017 | 25 | | | | 28/06/2017 | 25 | | | | 29/06/2017 | 25 | 500 | | +------------+-------+--------+----------+

ahmed salah · Answer

Description for above I have many Tours or programs every tour have more durations 81521 every duration have more details day1day2day3 every details or days have filight and transfer and excursion and accomodition(hotels) How to calculate cost per every days per each program ? start tour determin by flight and end determin by tour duration flight on date 01/07/2017 for tour alexia meaning 01/07/2017 + 8 = 09/07/2017 screen shoot from final result

ahmed salah · Answer

also any data you need not hisitat to ask me

ahmed salah · Answer

my problem How to get costs of Hotel + flight + transfer + Excursion per tour ? example 01/01/2017 08/07/2017 alexia 8days 400$ 01/09/2017 09/09/2017 alexia 8days 500$ In title of topic there are one wrong flight + transfer + excursion + transfer and correct is Hotel + flight + transfer + Excursion

ahmed salah · Answer

Tour : is fixed program tourism or fixed way meaning Tour amon :Day1 : visit pyramides Day2 : visit musuem luxor etc ... Tour Tables 1 - Tour Table PackageID PackageName BestimID Duration ParentID 1 alexia 2 sfinx 3-TourType PackageTypeID PackageType 1 sunsee 2-NileCruse 4- TourDuration PackageDurationsID PackageID PackageDuration NightCounts 1 alexia 8 days 7 1 alexia 15 days 14 5- DurationDetails DetailsDurationID PackageDurationsID Days FlightTypeID ProductID ExcursionID TransferTypeID day1 basmahotel from amsterdam to luxor day2 basmahotel day3 basmahotel day4 from luxor to amsterdam Transfer Tables 1-TransferType TransferTypeID TransferType 2- TransferPeriod TransferID FromDate ToDate TransferTypeID Price Margin Excursion Tables 1- Excursion Table ExcursionID ExcursionName BestimID ExcursionTypeID 1 visit pyramide 1 sun 2 diving water 2- ExcursionType ExcursionTypeID ExcursionType 3- ExcursionPeriod ExcursionPeriodID FromDate ToDate ExcursionID ExcursionPrice 01/01/2017 05/05/2017 20 06-05-2017 10/10/2017 30 Hotel Tables 1-Hotel Table ProductID ProductName BestimmingID TypeID 2- HotelPrice ProductPriceID ProductID FromDate ToDate HotelPrice 3- RoomType RoomTypeID RoomType 4-RoomStock RoomStockID RoomTypeID Stock RoomStockPrice 5-RoomService RoomServiceID RoomService ProductID ServiceTypeID ServicePrice 6-ServiceType ServiceTypeID ServiceType 7-RoomOccupation RoomOccupationID OccupationNo MaxAdult MaxOccupation OccupationPrice ProductID RoomTypeID 8-ChildrenDiscount ChildrenDiscountID ProductID MinAge MaxAge Discount FlightTables 1- FlightData Table FlighID FlightNo FlightDate FlightTypeID AirLineID Arrival Departure AdultPrice ChildPrice Stock TotalPrice Active 2-FlightRoute FlightTypeID FlightFrom FlightTo Active FlightRouteWay amsterdam luxor amsterdam to luxor luxor amsterdam luxor to amsterdam TablesBooking 1- UserTable UserID UserName Phone Emails 2-OrderTable OrderNo OrderDate UserID PackageID Price FromDate ToDate Period Invoice 3-Tour Table PackageID PackageName BestimID Duration ParentID 4-PackageFlight PackageID FlightID FromDate ToDate MonthNo

ahmed elbarbary · Answer

I can give you any details you need i will prepare it and put here

Vivek Sheth · Answer

Hello Ahmed Could you please tell me which table you are refering for flighttransferexcursiontransfer ? We also need information like how many tables are involved in one tour booking so based on that we can create query for cost. Please provide us dummy data for tables if possible. -Thanks.