Re: How to search between dates

I would use PL/SQL or C, but it could be done with an ugly join. I would also suggest that you want your availability to begin AFTER the EndDate of an existing reservation.

Select EquipID, EquipName,

    A.EndDate Available_From,
    B.StartDate Available_To
From EquipReserve A, EquipReserve B
Where B.EquipID = A.EquipID
  And A.EndDate < B.StartDate -- or <=
And Not Exists (

    Select 0 From EquipReserve C, EquipReserve D

    Where C.EquipID = D.EquipID
      And C.EndDate < D.StartDate -- or <=
      And C.StartDate = A.StartDate
      And C.EndDate = A.EndDate
      And D.StarteDate < B.StartDate)

If you allow StartDate to equal EndDate in a reservation, you could get redundant rows where this occurs. A Select Distinct would take care of the redundancy, but you would still see that date as available. You could add, just above the Order by, "And B.StartDate > A.EndDate" (and the "Distinct" would not be needed).

"Arun Solleti" <asolleti_at_csee.usf.edu> wrote in message news:395A8C35.B0B55893_at_csee.usf.edu...
> Hi
>
> Can this be done ?
>
> I have table which will contain the reservation schedule of an equipment
> in a lab. The table will look like below:
>
> UserID EquipID EquipName StartDate EndDate
> xyz 123 Name1 06/28/00 10/28/00
> abc 123 Name2 11/28/00 12/28/00
> efg 123 Name3 05/06/01 12/04/02
>
>
> So different users depending upon there needs will reserve the
> equipment.
>
> If i want to search for the availability of EquipID:123, and specify a
> start date and end date. How should i parse through the above table, to
> give search results about its avilability(i.e. it is avilable from
> 10/28/00 - 11/28/00 and again avialable form 12/28/00 - 05/06/00 and not
> in-between)
>
> Thanks
> Arun
>
Received on Thu Jun 29 2000 - 00:00:00 CEST