Member Avatar for _1_14

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)
  • 2 Contributors
  • 1 Reply
  • 374 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by rubberman

Dani AI

Generated

— the simplest, reliable approach is: (1) store a concrete start date for the duration, (2) make sure each DurationDetail row encodes a day offset (Day1, Day2, …) or an explicit day number, and (3) use a single procedure that enumerates every date in the range, looks up the hotel price row that covers that date and the flight row for that date, then inserts (or upserts) into DurationCost. is right to flag missing constraints and bad data types — fix those first (proper DATE types, numeric currency columns, and foreign keys from DurationDetail to Hotel and FlightData).

Below is a compact stored-proc you can adapt. It:

  • generates the date list between @StartDate and @EndDate,
  • maps each date to the matching DurationDetail row by parsing the DayN value,
  • left-joins HotelPrice using date BETWEEN FromDate AND ToDate,
  • left-joins FlightData where FlightDate = that date,
  • inserts the computed costs into DurationCost.

(For safety run the SELECT portion first before inserting; add a UNIQUE constraint on (DurationNo,[Date]) or use MERGE if you need upsert behavior.)

CREATE PROCEDURE dbo.sp_PopulateDurationCosts
  @DurationNo INT,
  @StartDate DATE,
  @EndDate   DATE
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH nums AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM sys.all_objects
  ),
  dates AS (
    SELECT DATEADD(day, n, @StartDate) AS [Date]
    FROM nums
    WHERE n <= DATEDIFF(day, @StartDate, @EndDate)
  ),
  detail AS (
    SELECT DurationNo, HotelID, FlightID,
           TRY_CAST(REPLACE([Days],'Day','') AS INT) AS DayNum
    FROM DurationDetail
    WHERE DurationNo = @DurationNo
  )
  INSERT INTO DurationCost (DurationNo, [Date], Hote_cost, Flight_cost, Transfer_cost)
  SELECT
    @DurationNo,
    d.[Date],
    hp.HotelPrice,
    fd.Price,
    0
  FROM dates d
  LEFT JOIN detail det ON DATEADD(day, det.DayNum - 1, @StartDate) = d.[Date]
  LEFT JOIN HotelPrice hp ON hp.ProductID = det.HotelID
       AND d.[Date] BETWEEN CAST(hp.FromDate AS DATE) AND CAST(hp.ToDate AS DATE)
  LEFT JOIN FlightData fd ON fd.FlighID = det.FlightID
       AND CAST(fd.FlightDate AS DATE) = d.[Date];
END

Troubleshooting tips: test the SELECT portion first; ensure HotelPrice ranges cover every date (or handle NULLs); confirm FlightData.Price is per passenger and multiply if needed; add indexes on HotelPrice(FromDate,ToDate,ProductID) and FlightData(FlighID,FlightDate) for performance.

Member Avatar for rubberman

First, create all of your database tables BEFORE you start populating them! What about foreign keys/links between the tables? Also, in your "HotelPrice" table there is no real way to tell to which hotel you are referring. This is just sloppy.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.