I need BETWEEN on speed

[Rizyak]

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out
and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way
to use BETWEEN that would be great. I don't think that code is necessary at
this juncture, so save the 'Please post code' post :) Thanks.

[Aaron [SQL Server MVP]]

If there is an index on the datetime column, then use >= and < rather than
BETWEEN.

But as soon as you add an OR clause, this might really muck up the plan.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way to use BETWEEN that would be great. I don't think that code is necessary at this juncture, so save the 'Please post code' post :) Thanks.

[Jacco Schalkwijk]

starttime <= 6 AND endtime >= 4

--
Jacco Schalkwijk
SQL Server MVP
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way to use BETWEEN that would be great. I don't think that code is necessary at this juncture, so save the 'Please post code' post :) Thanks.

[Russell Fields]

Rizyak,

EventStartTime <= BetweenEndTime
AND
EventEndTime >= BetweenStartTime

Russell Fields
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way to use BETWEEN that would be great. I don't think that code is necessary at this juncture, so save the 'Please post code' post :) Thanks.

[Bernie Velivis]

Assuming an event is selected if it was busy at least part of the time
during the search window...

Select events from table where
(EventStartTime between WindowStartTime and WindowsEnd Time) or
(EventEndTime between WindowStartTime and WindowsEnd Time) or
(EventStartTime <= WindowsStartime and EventEndTime >= WindowEndTime)
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way to use BETWEEN that would be great. I don't think that code is necessary at this juncture, so save the 'Please post code' post :) Thanks.

[Chris Hohmann]

"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those
by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded out and worked, just a missight in theory is as follows. I did a BETWEEN call
that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.
As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better way to use BETWEEN that would be great. I don't think that code is necessary at this juncture, so save the 'Please post code' post :) Thanks.

SELECT
<field list>
FROM
Events AS E
WHERE
E.Begin_Time < '2004-06-29T18:00:00.000' AND
E.End_Time > '2004-06-29T16:00:00.000'

In the future, please provide DDL, sample data and desired output.

[Rizyak]

This works. Thank you.

"Jacco Schalkwijk" <ja****************@to.newsgroups.mvps.org.invalid > wrote
in message news:%2****************@TK2MSFTNGP09.phx.gbl...

starttime <= 6 AND endtime >= 4

--
Jacco Schalkwijk
SQL Server MVP
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cb**********@gnus01.u.washington.edu...

This is x-posted in:
alt.php.sql
comp.databases.ms-sqlserver
microsoft.public.sqlserver.programming

I have events that occur during the day. I want to be able to search those by a form with checkboxes (multiple select).

Let's say for instance an event is happening from 3-10pm. When someone
searches for 4-6 (checkbox option) it needs to show up.

I don't need code so much as I just need theory. My theory that I coded

out

and worked, just a missight in theory is as follows. I did a BETWEEN call that pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6. As you can see. The event spans that time, but does not start or stop
between 4 and 6, thus was not pulled. Ooops.

So if someone call tell me of another function or perhaps just a better

way

to use BETWEEN that would be great. I don't think that code is necessary

at

this juncture, so save the 'Please post code' post :) Thanks.

[--CELKO--]

>>I don't need code so much as I just need theory. <<

Theory? Okay! Draw a picture of a time-line. When do two duration
NOT overlap? When T1 begins after T2 ends and when T1 ends before T2
begins. Therefore, When do two duration overlap? When they do not not
overlap!

Another trick is to use a NULL for "eternity" and then write
COALESCE (finish_date, CURRENT_TIMESTAMP) in your queries.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

Another trick is to use a NULL for "eternity" and then write
COALESCE (finish_date, CURRENT_TIMESTAMP) in your queries.

I don't know what you are thinking of here, but when I have an open
time interval, I rather use '99991231' than CURRENT_TIMESTAMP. If the
interval you are comparing with is partly in the future, CURRENT_TIMESTAMP
might not give the correct results.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Joe Celko]

>> ... when I have an open time interval, I rather use '99991231' than
CURRENT_TIMESTAMP. <<

If I have an event that is still in progress, such as checking into a
hotel, then the only real knowledge I have is that Mr. X is still here,
right now. I can figure out his current bill, etc. in a simple VIEW.
The NULL is easy to spot and to handle for this purpose.

If you use a dummy date, then you need to be sure that:

1) you always use extra code to handle it correctly in calculations and
queries.

2) And what if your personnal choice for a dummy date is an actual value
in the data? Look at all the SQL Server programs that assume the world
began in 1900 because of converting a zero to a datetime.

3) Dummy dates do not port very well; Oracle can store BCE dates, DB2
has a greater range, etc.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Joe Celko (jc*******@earthlink.net) writes:

... when I have an open time interval, I rather use '99991231' than

CURRENT_TIMESTAMP. <<

If I have an event that is still in progress, such as checking into a
hotel, then the only real knowledge I have is that Mr. X is still here,
right now. I can figure out his current bill, etc. in a simple VIEW.
The NULL is easy to spot and to handle for this purpose.

Agreed, I don't question that part. What I meant to say is that

SELECT *
FROM tbl
WHERE coalesce(enddate, CURRENT_TIMESTAMP) > @stopdate

could give in correct result, if both @stopdate and endate are in
the future. (Say for instance that tbl holds contracts that can be
open-ended, but also have future date at which they expire. Thus
I would rather write that as:

SELECT *
FROM tbl
WHERE coalesce(enddate, '99991231') > @stopdate

(Note: the above is for performance reasons better written as
enddate IS NULL OR enddate > @stopdate in SQL Server.)

Using special values to mean something is certainly not good practice,
because if you forgot if you used 99991231 or 21010101 or whatever, you
may get the wrong result.
If you use a dummy date, then you need to be sure that:

Agreed on all your points.

I have to admit that I have committed the sin at least once. I have
a table cross-currency pairs, and there is a column fixedfrom which
tells you from which date the currency rate for this pair has been
fixed. In practice there are three(*) possible values: NULL (the rate
is not fixed), 19990101 DEM/EUR, ITL/EUR etc and 17530101 for
SEK/SEK, USD/USD etc. In this case NULL was not available to mean
"has always been". (Of course, I could have added an extra column
which would have specified that fixedfrom had any meaning at all,
but was less appetizing. Even if that is the way it looks in the GUI.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rizyak]

********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

Why doesn't this work:

SELECT *
FROM 'Events'
WHERE dayofweek
REGEXP 'monday' OR description REGEXP 'monday'

Just so you know, this does work:
SELECT *
FROM `Events`
WHERE dayofweek
REGEXP 'monday'

[Erland Sommarskog]

Rizyak (ry**********@latitude47.comANDMETOO) writes:

********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

Why doesn't this work:

SELECT *
FROM 'Events'
WHERE dayofweek
REGEXP 'monday' OR description REGEXP 'monday'

Just so you know, this does work:
SELECT *
FROM `Events`
WHERE dayofweek
REGEXP 'monday'

Not that I know what you are talking about, because there are several
errors in both samples as far as SQL Server is concerned.

However, I like to point out a general issue: don't just say "does not
work", but specify. Do you get an error message? Do you get unexpected
results.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp