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 !
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 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
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'
SELECT 400,CONVERT(DATETIME, '17/05/2003', 103), CONVERT(DATETIME, '01/06/2003', 103), 'SINGLE'
SELECT 500,CONVERT(DATETIME, '02/06/2003', 103), CONVERT(DATETIME, '01/09/2003', 103), 'SINGLE'
SELECT 250,CONVERT(DATETIME, '02/09/2003', 103), CONVERT(DATETIME, '30/10/2003', 103), 'SINGLE'
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'
DATEDIFF(D,BEGIN_PERIOD, END_PERIOD) AS PERIODDAYS,
DATEDIFF(D,BEGIN_PERIOD, @START) AS STARTDIFF,
DATEDIFF(D,@END, END_PERIOD) AS ENDDIFF
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.