asked on

Date Intervals Problem?

Iam using a table (Validity):

HotelID (int)
V_From (small date)
V_To (small Date)
Price (money)

I want to query about hotels that available on some interval, but may more than one record for the same hotel covered my period for example :

i need to know all the hotels that available from 1/2/2002 to 15/2/2002

the answer may like:

HotelID V_From V_To Price
====== ====== ========= =====
10 20/01/2002 04/02/2002 50
10 04/02/2002 17/02/2002 45
22 01/01/2002 15/04/2002 60
27 01/02/2002 05/02/2002 40
27 06/02/2002 10/02/2002 45
27 11/02/2002 25/02/2002 35

Guy Hengel [angelIII / a3]

In fact, both the start date and the enddate of the interface need to be between the V_From and V_To, so the answer should be easy:

SELECT *
FROM Validity
WHERE '2002/02/01' BETWEEN V_From AND V_To
AND '2002/02/15' BETWEEN V_From AND V_To

CHeers

Shivshankar

I guess it should be the other way around...

SELECT * FROM Validity
WHERE V_From BETWEEN '2002/02/01' AND '2002/02/15'
OR V_To BETWEEN '2002/02/01' AND '2002/02/15'

I'm not sure anyway... :)

alhanouty

ASKER

please note the two intervals above:

20/01/2002 - 01/02/2002
04/02/2002 - 17/02/2002

niether the first nore the second satisfy the criteria you mentioned but both of the (union) must be in the answer as i mentioned above.

i mean may one interval not satisfy the condition but if joined to other interval(s), it may be.

Anthony Perkins

Please maintain your open questions:

Inserting ActiveX in Crystal Report? Date: 12/11/2001 11:01PM PST
http://devx.experts-exchange.com/jsp/qShow.jsp?ta=crystal&qid=20245507
DataGrid Alignment Problem. Date: 11/17/2001 11:13AM PST
http://devx.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20235013
VB with SQL7.0 Date: 05/02/2001 01:27AM PST
http://devx.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20112276

Anthony

alhanouty

ASKER

to acperkins

I did, any help now?

thankx

Anthony Perkins

Thanks, I appreciate you taking care of that.

Anthony

Guy Hengel [angelIII / a3]

Then, assuming that only 2 periods can join (ignoring for the moment the question of the price):

SELECT v.HotelID, v_from, v_to , Price
FROM Validity V
WHERE '2002/02/01' BETWEEN V_From AND V_To
AND '2002/02/15' BETWEEN V_From AND V_To
UNION
SELECT v1.HotelId, v1.v_from, v2.v_to, v1.Price
from Validity v1 join validity v2
ON v1.HotelID = v2.HotelID
AND v1.v_to = v2.v_From
AND '2002/02/01' BETWEEN v1.V_From AND v2.V_To
AND '2002/02/15' BETWEEN v1.V_From AND v2.V_To

The question of the price might be a rule depending on the highest or lowest price, maybe average...

Again, if more than 2 periods could "join" together, query gets more complex...

CHeers

alhanouty

ASKER

to angelIII :

I appreciate you for you solution, but I dont know how many records(periods) in my database, may 1,2,.. for the same hotel...

again many thankx

CleanupPing

alhanouty:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial