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

#1 ahmedba  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 141
  • 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  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 141
  • 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) 

Was This Post Helpful? 0
  • +
  • -

#3 ahmedba  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 141
  • 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
Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13553
  • View blog
  • Posts: 54,090
  • 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?
Was This Post Helpful? 0
  • +
  • -

#5 ahmedba  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 141
  • 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

Was This Post Helpful? 0
  • +
  • -

#6 ndc85430  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 611
  • View blog
  • Posts: 2,582
  • 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.
Was This Post Helpful? 0
  • +
  • -

#7 ahmedba  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 141
  • 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

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13553
  • View blog
  • Posts: 54,090
  • 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

Wait.. didn't you already have a thread on this same topic?

Merging.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1