How to add cost per hotel and flight in duration cost table

Transact-SQL
,
1

Problem

How to get cost per hotel and flight then add it in duration cost table based on flight date .

Details

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 flight

then 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,
)

Image for final result i need

1.jpg1440×900 109 KB