November 18, 2010 at 8:37 am
didier.boelens (11/18/2010)
Stefan Krzywicki,I mean that it will create many records for each reservation. Meaning the database will grow...
Most databases grow.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 18, 2010 at 8:42 am
didier.boelens (11/18/2010)
I use Julian Dates to obtain an integer value which makes the query faster...
And that speed isn't negated by the functions used to switch your start and end dates to Julian?
If you want to stick to Julian, I'd advise setting your start & End dates as variables and doing the function up in the SET @MyVar statement instead of the WHERE of your T-SQL where you potentially risk RBAR or non-SARGable functionality.
But this suggestion is a personal preference and untested in regards to performance for your particular setup. I try to minimize my use of functions in WHERE clauses when I can.
November 18, 2010 at 8:44 am
didier.boelens (11/18/2010)
GSquared, because I can't find a way of building a SQL statement that would satisfy the following requirement:I there anything available between date1 and date2.
If I take the following example:
reservation, between 05Nov2010 and 15Nov2010
If I am looking for houses which are free between 07Nov2010 and 17Nov2010, how can I build the query?
05Nov < 07Nov and 15Nov<17Nov...
Now if you have a solution, I will be more than happy
Since you're using integers for dates, you should be able to query ranges pretty easily.
You can either do it with an OR statement (or a Union) with a check if the start value or the end value is between the desired dates, or if the start value is before or equal and the end value after or equal to.
E.g.:
select 5 as StartDate, 15 as EndDate into #T;
select *
from #T
where StartDate >= @RequestedStart and StartDate <= @RequestedEnd
or EndDate <=@RequestedEnd and @EndDate >= @RequestedStart
or StartDate <= @RequestedStart and EndDate >= @RequestedEnd;
That would give you a list of the rows that overlap the requested time period. If that's got house IDs in it, you could use it in a Where Not In statement. Or you could invert the logic in the Where clause outlined above and get it directly, whichever is easier for you.
This works because there are basically three conditions that cause a range-overlap.
If you change to using date datatypes, you query it the same way, looking for range-overlaps. The data just looks different.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 8:48 am
Can you provide sample data for the two tables so we can give you the best solutions (and test them)?
November 18, 2010 at 9:59 am
Are you sure you understand your business requirements? Beach house rental agreements I've seen have a time grain of one week, typically Saturday to Saturday, however, within a rental agency's inventory, could also include Sun-Sun or rarely Fri-Fri (granted, this may be a statutory result within the United States: in most US states, if you lease real estate for less than one week, the government believes you are materially operating as a hotel or boarding house, and you are subject to different taxes and regulations regarding the process by which you can declare someone to be trespassing).
I observe this because in this business environment, both customers will have a natural language belief that they have a reservation that includes the turnover day, say, Saturday, 11/20/2010; it happens that one has to be out by 11, the other can start after 3 (or whatever the terms might specifically be). Even if your region allows rentals by the day, your present query is going to lose the last half day from a previous rental.
I would also observe, if the business you were modelling was like US beach houses, this specific technical issue of day ranges that you've painted yourself into wouldn't be necessary.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply