How to calculate cost per every day for flight and transfer and hotel

Page 1 of 1

7 Replies - 1057 Views - Last Post: 07 July 2017 - 09:27 AM

#1 ahmedba

Reputation: 0
• Posts: 161
• Joined: 24-January 14

How to calculate cost per every day for flight and transfer and hotel

Posted 25 June 2017 - 03:14 PM

How to design database for tourism company to calculate cost of flight and hotel per every program tour based on date ?

what i do is

Table - program

```+-----------+-------------+
| ProgramID | ProgramName |
+-----------+-------------+
|         1 | Alexia      |
|         2 | Amon        |
|         3 | Sfinx       |
+-----------+-------------+
```

every program have more duration may be 3 days or 5 days only

it have two periods only 3 days or 5 days .

so that i do duration program table have one to many with program .

Table - ProgramDuration

```+------------+-----------+---------------+
| DurationNo | programID |   Duration    |
+------------+-----------+---------------+
|          1 |         1 | 8 for Alexia  |
|          2 |         1 | 15 for Alexia  |
+------------+-----------+---------------+
```

And same thing to program amon program and sfinx program 3 and 5 .

every program 3 or 5 have fixed details for every day as following :

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 |                   |
+------+--------+--------------------+-------------------+
```

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 |          |
+------------+-------+--------+----------+
```

And this is actually what i need how to make relations ship to join costs with program .

for flight and hotel costs as above ?

for 5 days cost will be 1200

25 is cost per day for hotel Hilton

500 is cost for flight

20 and 55 is cost per transfers

Is This A Good Question/Topic? 0

Replies To: How to calculate cost per every day for flight and transfer and hotel

#2 ahmedba

Reputation: 0
• Posts: 161
• Joined: 24-January 14

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 27 June 2017 - 05:13 PM

Problem

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

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,
HotelID int null,
FlightID int null

)
insert into DurationDetail values (2,'Day1',1,'amsterdam to luxor','airport to hotel'), (2,'Day2',1,null,'AbuSimple musuem'),
(2,'Day3',1,null),
(2,'Day4',1,null),
(2,'Day5',1,'Luxor to amsterdam')
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,
[AirLine] [nvarchar](50) NULL,
[Arrival] [time](7) NULL,
[Departure] [time](7) NULL,
[Price] [money] NULL,
[Active] [bit] NULL
)
insert into FlightData values (1,'ms300',1,'egyptairline','6','10',200,1)

CREATE TABLE [dbo].[FlightRoute](
[FlightTypeID] [int] NOT NULL,
[FlightFrom] [nvarchar](max) NULL,
[FlightTo] [nvarchar](max) NULL,
[Active] [bit] NULL,
[FlightRouteWay] [nvarchar](max) NULL,
)
insert into FlightRoute values (1,'amsterdam','cairo',1,amsterdam to cairo), (2,'cairo','amsterdam',1,cairo to amsterdam)
CREATE TABLE [dbo].[Hotel](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](50) NULL,

)
insert into Hotel values (1,'Hilton'), (2,'Movenpick')

CREATE TABLE [dbo].[HotelPrice](
[ProductPriceID] [int] NOT NULL,
[ProductID] [int] NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[HotelPrice] [numeric](18, 0) NULL,
)
insert into HotelPrice values (1,1,01/07/2017,01-09-2017,20\$), (2,1,02-09-2017,02-11-2017,30)
```

#3 ahmedba

Reputation: 0
• Posts: 161
• Joined: 24-January 14

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 08:51 AM

Problem

Hotel HotelID have relation with package and in same time have relation with daydetails table duplicate

#4 modi123_1

• Suitor #2

Reputation: 14576
• Posts: 58,439
• Joined: 12-June 08

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 08:53 AM

Ah, what?

#5 ahmedba

Reputation: 0
• Posts: 161
• Joined: 24-January 14

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 08:57 AM

meaning it is wrong or correct

This post has been edited by ahmedba: 07 July 2017 - 08:57 AM

#6 ndc85430

• I think you'll find it's "Dr"

Reputation: 902
• Posts: 3,638
• Joined: 13-June 14

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 09:12 AM

Your first post made no sense at all. Please explain in more details what you're asking. It appears the link you posted to the image is broken, too.

#7 ahmedba

Reputation: 0
• Posts: 161
• Joined: 24-January 14

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 09:20 AM

I need to design database for booking tours for tourism company

Tourist booking package have two type :

Reservation hotel : this is will by 4 days or 6 days .

Suppose he select hotel Hilton 4 days meaning 3 Nights include ( accommodation costs + flight costs + transfer costs )

Reservation Program: this will be 4 days or 6 days only

Suppose he select program alexa 4 days meaning 3 Nights ( accommodation costs + flight costs + transfer costs + excursions costs)

Accommodation cost include room price per days + services depend on periods

Meaning room price from 01/01/2017 to 01/04/2017 different from 01/05/2017 to 01/09/2017

Flight include internal flights and return back to his country

Transfer : include internal transfer from airport to hotel .

Price of transfers from date 01/01/2017 to 01/04/2017 different from 02/04/2017 to 02/12/2017

Excursions : visit musumes like that and this is represent cost of visit

Excursion price depend on periods .

Price of excursion from date 01/01/2017 to 01/04/2017 different from 02/04/2017 to 02/12/2017

And excursion only found on programs not hotel reservation

8 days for hotel or program start from flight date

Every flight have more than one package (reservation hotel or program)

Final Result as below :

```Package 1 4 days

day              flight                    hotel
day1             amsterdam to aswan         Hilton
day2                                        Hilton
day3                                        Hilton
day4            aswan to amsterdam
Package 1 4 days costs(Here actually my problem )

day              flightcosts                 hotelcosts
26/07/2017           500                           50
27/07/2017                                         50
28/07/2017                                         50
29/07/2017          500
```

This post has been edited by ahmedba: 07 July 2017 - 09:21 AM

#8 modi123_1

• Suitor #2

Reputation: 14576
• Posts: 58,439
• Joined: 12-June 08

Re: How to calculate cost per every day for flight and transfer and hotel

Posted 07 July 2017 - 09:27 AM