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 ?

thanksThere 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!|||thank you,
I'll try your way which seems to match my case

cheers|||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!|||http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=27064