Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Unhappy Unanswered: complex select, please help

    I have a table of hotelRoomPrices with the folowing fields:
    PricePerNight,BeginPeriod (which is the date from which this price is correct),EndPeriod (which is the price until which
    the price is correct),TypeOfRoom

    in a web form the user enter the dates he want to stay in the hotel and I must compute the price.
    The problem is when the selected dates lie over 2 periods (or even 3 periods)

    ie: I have a price from june 1st to june 15th and another price from june 16th to july 1st. The user want to go to the hotel from june 10th to june 20th !

    can somebody help me ???

    thanks

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    There are two approaches to this. You could use a Tally table Use which is basically a single column (int) table containing all positive integers between 0 and N ... (N being sufficiently large enough to satisfy your needs) or you could use the following method which is simpler.

    I created a table with the following structure :

    Price_per_night,
    Begin_Period
    End_Period
    Type_Of_Room

    I think the structure is self explanatory. I also chose the following booking dates starting 21/05/2003 and ending on the 23/10/2003. This places the booking time under three price groups for my given example. See code below

    -- GENERATE SOME TEST RECORDS

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HOTELROOMPRICES')
    DROP TABLE HOTELROOMPRICES

    CREATE TABLE HOTELROOMPRICES (
    PRICE_PER_NIGHT NUMERIC(9,2),
    BEGIN_PERIOD DATETIME,
    END_PERIOD DATETIME,
    TYPE_OF_ROOM VARCHAR(20)
    )

    INSERT INTO HOTELROOMPRICES(PRICE_PER_NIGHT, BEGIN_PERIOD, END_PERIOD, TYPE_OF_ROOM)
    SELECT 350,CONVERT(DATETIME, '01/05/2003', 103), CONVERT(DATETIME, '16/05/2003', 103), 'SINGLE'
    UNION ALL
    SELECT 400,CONVERT(DATETIME, '17/05/2003', 103), CONVERT(DATETIME, '01/06/2003', 103), 'SINGLE'
    UNION ALL
    SELECT 500,CONVERT(DATETIME, '02/06/2003', 103), CONVERT(DATETIME, '01/09/2003', 103), 'SINGLE'
    UNION ALL
    SELECT 250,CONVERT(DATETIME, '02/09/2003', 103), CONVERT(DATETIME, '30/10/2003', 103), 'SINGLE'
    UNION ALL
    SELECT 400,CONVERT(DATETIME, '31/10/2003', 103), CONVERT(DATETIME, '31/12/2003', 103), 'SINGLE'

    -- WORKS OUT THE COST OF BOOKING THE ROOM FOR THE PERIOD 21 MAY 2003 - 23 OCTOBER 2003

    DECLARE @START DATETIME
    DECLARE @END DATETIME

    SET @START = '5/21/2003'
    SET @END = '10/23/2003'

    SELECT SUM(PRICE_PER_NIGHT * PERIODDAYS -
    CASE WHEN STARTDIFF > 0 THEN STARTDIFF * PRICE_PER_NIGHT ELSE 0 END -
    CASE WHEN ENDDIFF > 0 THEN ENDDIFF * PRICE_PER_NIGHT ELSE 0 END) AS 'COST OF HOLIDAY'
    FROM
    (
    SELECT PRICE_PER_NIGHT,
    DATEDIFF(D,BEGIN_PERIOD, END_PERIOD) AS PERIODDAYS,
    DATEDIFF(D,BEGIN_PERIOD, @START) AS STARTDIFF,
    DATEDIFF(D,@END, END_PERIOD) AS ENDDIFF
    FROM HOTELROOMPRICES
    WHERE @START BETWEEN BEGIN_PERIOD
    AND END_PERIOD OR @END BETWEEN BEGIN_PERIOD
    AND END_PERIOD OR (@START <= BEGIN_PERIOD AND @END >= END_PERIOD)) AS A

    The above query returns a value of 62650 which I believe is the correct amount of money for the room.

    Good Luck!
    Shadow to Light

  3. #3
    Join Date
    Mar 2003
    Location
    France
    Posts
    21
    thank you,
    I'll try your way which seems to match my case

    cheers

  4. #4
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Originally posted by djiff
    thank you,
    I'll try your way which seems to match my case

    cheers
    I must admit that the majority of the code was written by a very celever SQL Developer (not me) so I will pass on your thank you to him as well!

    Good Luck!
    Shadow to Light

  5. #5
    Join Date
    Jun 2003
    Posts
    1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •