How to add cost per hotel and flight in duration cost table
-
Problem
How to get cost per hotel and flight then add it in duration cost table based on flight date
suppose i write flight date
26/07/2017 alexia 8days 04/08/2017
it must automatically get cost from hotel price table and price from flightthen add it in duration cost table
so that what query i write to get cost per hotel and flight when write flight date then insert it to duration cost table
my database
CREATE TABLE program(
ProgramID int primary key not null,
ProgramName varchar(30)
)
GO
insert into program values(1,'Alexia'),(2,'Amon'),(3,'Sfinx')
GO
CREATE TABLE ProgramDuration(
DurationNo int primary key not null,
programID int not null,
Duration varchar(30) null
)
insert into ProgramDurationvalues(1,1,'3 for Alexia'),(2,1,'5 for Alexia')
GO
CREATE TABLE DurationDetail(
DurationNo int not null,
[Days] varchar(20) not null,
Hotel varchar(20) null,
Flight varchar(50) null,
transfers varchar(50) null
)
insert into DurationDetail values (2,'Day1','Hilton','amsterdam to luxor','airport to hotel'), (2,'Day2','Hilton',null,'AbuSimple musuem'),
(2,'Day3','Hilton',null,null),
(2,'Day4','Hilton',null,null),
(2,'Day5','Hilton','Luxor to amsterdam',null)
GO
CREATE TABLE DurationCost(
DurationNo int not null,
[Date] date not null,
Hote_cost numeric(18,0) null,
Flight_cost numeric(18,0) null,
Transfer_cost numeric(18,0) null
)
insert into DurationCostvalues(
2,'2017-06-25',25,500,20),
(2,'2017-06-26',25,null,55),
(2,'2017-06-27',25,null,null),
(2,'2017-06-28',25,null,null),
(2,'2017-06-29',25,500,null)
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,
)CREATE TABLE [dbo].[FlightRoute](
[FlightTypeID] [int] NOT NULL,
[FlightFrom] [nvarchar](max) NULL,
[FlightTo] [nvarchar](max) NULL,
[Active] [bit] NULL,
[FlightRouteWay] [nvarchar](max) NULL,
)CREATE TABLE [dbo].[Hotel](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](50) NULL,
[BestimmingID] [int] NULL,
[TypeID] [int] NULL,
)CREATE TABLE [dbo].[HotelPrice](
[ProductPriceID] [int] NOT NULL,
[ProductID] [int] NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[HotelPrice] [numeric](18, 0) NULL,
) -
This seams pretty simple; what have you tried so far? Where are you stuck?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
I need to get cost of hotel and flight based on table duration details table as following
-
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
every program determine starting by flight date so that if flight date is 25/06/2017 for program alexia 5 days it will be as following
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 |
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply