Gaps in Dates

[Trancemission]

I am working on a hotel project and am looking to improve a function which has been written to check for dates in a table. The function is ugly and very slow (uses text file dumps etc.... no I don't want to amend it)

I have a table of hotels, each hotel can have multiple room types. Each room type should have associated records in another table with bands of dates (01/01/04 - 29/12/05) and prices.

I need to ensure that these bands of dates do not have gaps in. There can be an infinite number of date bands (usually 3-4 though)



Some helpful (?) examples

Hotels
------
Hotel Name    | Room Type    | RoomTypeID
-----------------------------------------
Some Hotel    | Some Room1   |   1
Some Hotel    | Some Room2   |   2


Room Types
----------
RoomTypeID    |Hotel ID | Start Date | End Date | PricePerNight
-----------------------------------------------------
1             | 1       |01/01/04    | 14/02/04 | 55
1             | 1       | 15/02/04   | 31/05/04 | 75
1             | 1       | 01/07/04   | 31/12/04 | 155

So in the above data I need to see that there is a gap between 31/05/04 and 01/07/04.

The data above is only demo to get theory.

I am having a real headache, i am currently learning more and more of access but still have not mastered creating recordsets whilst looping through another recordset.

Any help teaching this unix man some windows skills welcome


Trancemission
=============
If it's logical, it'll work!

 

[SarahG]

OK this is how I would do it.

1. Create a table, called tblTempRooms with the fields:
ID (Autonumber)
HotelID
RoomType
StartDate
EndDate

2. Create a query which selects from your room type table order by Hotel, then RoomType, then StartDate. The order is crucial. This query appends rows into tblTempRooms.

3. Create a query "qryNext" which selects all the fields EXCEPT ID from tblTempRooms. This query will have an additional calculated field called NextRecord which is (ID-1).

4. Create a query which joins tblTempRooms and qryNext on Hotel, RoomType and tblTempRooms.ID=qryNext.NextRecord. Modify the WHERE clause so that it selects where
DateDiff("d", tblTempRooms.EndDate, qryNext.StartDate) >= 1
(i.e. difference in days >=1)

Create a function to :
A. Delete all rows from tblTempRooms
B. Run the append query mentioned in 2
C. Run the query mentioned in 4

I hope I've made sense, it's difficult to explain. TblTempRooms is only used to sort the rooms by hotel, type and date order and will be flushed out each time you run the function. By building qryNext, you are forcing it to look at itself and find the next record for each ID.
Good luck.

 

[PHV]

Something like this ?
SELECT A.* FROM [Room Type] A LEFT JOIN [Room Type] B
ON (A.RoomTypeID=B.RoomTypeID) AND (A.[Hotel ID]=B.[Hotel ID]) AND (A.[End Date]=B.[Start Date]-1)
WHERE B.RoomTypeID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trancemission]

Thanks SarahG, worked great [edited so datediff Check is >1 as my dates start from the next day]

The old 'function' i'm told used to have to be run over night

Instead of starting a new thread I am also having a little trouble running the append query:

cmd.ActiveConnection = cnn
cmd.CommandText = "qry_append_africa_final"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute

rst and cmd have been set etc...I get an error when trying to run about the field being to small to accept the data I am trying to paste.

I got the error a couple of days ago when an appened query was expecting a Paramater, i passed it and all worked fine. I don't need to add a paramater just run the query. [The query selects the data to append via a hardcoded where clause]

Any quick tips whould be great but I have copied and pasted the code from some which ran a delete query so will have a good read thorugh, again thanks for your help.

Marcus





Trancemission
=============
If it's logical, it'll work!

 

[SarahG]

Hi Marcus,
nothing springs to mind immediately, apart from the suggestion to run the query directly from access and see if you get the same problem and debug from there, taking out one field at a time to see which one is causing the problem, which you've probably done already.
Also, ensure none of the field names have changed since the query was saved, as this would have the same effect as a parameter query.
You may be as well off to start a new thread as a thread with zero replies gets more sympathy and might get you more help! Sorry I can't be more help.
Sarah.

 

[Trancemission]

Thanks again Sarah will address that little problem another day....

Evolving my date checking function, all works okay for positive date test - ie the gap is larger than one, but i also want to check that the date band's do not overlap.

This would produce a negative result in numOfDays difference, which is fine as I can check for these values the problem is I think I have gone wrong somewhere.

I have been using this to check all hotels @ once. Problem is if I check for a negative value i get a result for each hotel where it follows on the previous hotel in the temp table. I kind of understand how this works and can maybe work round:

I can check for a value larger that -500 [1.5 years] which is about the average length of each contract and filter these out but I am getting a little lost. Should I be able to check multiple hotels at once with your exmaple?

I have included the query which joins the tmpTable:

SELECT temp_hotels_dates.HotelName, temp_hotels_dates.RoomType, temp_hotels_dates.Occupancy, temp_hotels_dates.startdate, temp_hotels_dates.enddate, DateDiff("d",[temp_hotels_dates].[enddate],[qry_next_temp].[StartDate]) AS NumDays, temp_hotels_dates.FromDB, temp_hotels_dates.id, temp_hotels_dates.id
FROM qry_next_temp INNER JOIN temp_hotels_dates ON (qry_next_temp.RoomType = temp_hotels_dates.RoomType) AND (qry_next_temp.HotelName = temp_hotels_dates.HotelName) AND (qry_next_temp.NextRecord = temp_hotels_dates.id)
WHERE (((DateDiff("d",[temp_hotels_dates].[enddate],[qry_next_temp].[StartDate]))<>1));

You will notice I have added an occupancy field which should not cause any problems as when I add to the temp table the order is correct

It's friday afternoon and I am sure there is a way to get this working but my access knowledge is slowing me down [I could do this in unix no problems]

Anyways any [more] help would be appreciated

Thanks again


Trancemission
=============
If it's logical, it'll work!

 

[PHV]

Trancemission, just to know, have you tried my suggestion ?

 

[SarahG]

You should be able to run this for all hotels at once, the join on hotels should ensure that each comparison is within that hotel, the same applies for the roomtype.
Is it possible you have different hotels with the same name? e.g. Mariott Florida and Mariott Dublin may be both called 'Mariott' in your data... if so, switch to a join on Hotel ID. These would have a datediff of zero and so get through.
??

 

[Trancemission]

Think I have found the problem after reading your post, as I have mentioned I have added an occupancy field - and for got to join that .

Anyways just ran it again and got no results which should be a good thing

I will now go and change some dates and make sure that it picks these up - it should do

Thanks again for your help

Marcus

Trancemission
=============
If it's logical, it'll work!