Query with Interval of dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query with Interval of dates

Author

Topic

scozzese
Starting Member

12 Posts

Posted - 2003-07-03 : 08:41:13

I've two dates defined by a user by a form: from_date, to_date.
I've a table with two field: table.start_date, table.end_date.
Now I want to select record from the table with period (table.start_date, table.end_date) intersec period (from_date, to_date).
I find next solution but I don't like it:
select * from table where
(table.start_date < from_date and table.end_date > to_date) OR
(table.start_date < from_date and (table.end_date > from_date and table.end_date <= to_date)) OR
((table.start_date >= from_date and table.start_date < to_date) and table.end_date > to_date) OR
((table.start_date >= from_date and table.start_date < to_date) and
table.end_date > from_date and table.end_date <= to_date)).
Could you help me to find a better solutions. Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 11:18:24

quote:

I've two dates defined by a user by a form: from_date, to_date.

quote:

I've a table with two field: table.start_date, table.end_date.

You don't mean Just 2 fields right? If you do, then not OK, how would discern the difference between the rows.

quote:

Now I want to select record from the table with period (table.start_date, table.end_date) intersec period (from_date, to_date).

English please. Do you mean the dates between the dates on the form?

quote:

I find next solution but I don't like it:
select * from table where
(table.start_date < from_date and table.end_date > to_date) OR
(table.start_date < from_date and (table.end_date > from_date and table.end_date <= to_date)) OR
((table.start_date >= from_date and table.start_date < to_date) and table.end_date > to_date) OR
((table.start_date >= from_date and table.start_date < to_date) and
table.end_date > from_date and table.end_date <= to_date)).

Neither do I

quote:

Could you help me to find a better solutions. Thanks

Would love to. Supply to us in business terms what you are trying to do.

Brett

8-)

scozzese
Starting Member

12 Posts

Posted - 2003-07-03 : 11:41:34

You don't mean Just 2 fields right? If you do, then not OK, how would discern the difference between the rows.

Answer: no, I don't for example there is a Primary Key table.myPK

English please. Do you mean the dates between the dates on the form?
Answer:no I mean I want to select record from the table with period (table.start_date:table.end_date) that intersect period (from_date: to_date).

Thanks and Sorry for my english

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 12:32:12

My apologies...I didn't mean your english.

I mean to describe what you need in business terms.

Sorry again.

I need to understand what intersect means. Does it mean between, or an exact match or overlap. for example

Table:
1/1/2003 to 1/2/2003
Form:
1/1/2003 to 1/2/2003

That's an exact match

Table:
1/2/2003 to 1/3/2003
Form:
1/1/2003 to 1/5/2003

These dates are Between

Table:
1/1/2003 to 1/4/2003
Form:
1/2/2003 to 1/5/2003

These dates overlap

We need to know which one (I don't believe there any other combinations) you want.

And Why. What are you trying solve/support

And again sorry for the "English" comment.

Brett

8-)

scozzese
Starting Member

12 Posts

Posted - 2003-07-03 : 12:48:18

Don't worry I'm not angry I'm an Italian Spaghetti so I know my language deficiency , anyway I mean (exact match AND Between AND overlap).
I find another solutions little bit better:
NOT (table.start_date < from_date and table.end_date < from_date )
AND NOT (table.start_date > to_date and table.end_date > to_date )

I'm building a report about cost management.

Bye
;)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 13:33:42

quote:

anyway I mean (exact match AND Between AND overlap).

So the only thing left are dates totally outside? Like totally before or totally after?

OK Lets' eliminate them one by one

1. Exact, easy enough

table.start_date = from_date and table.end_date = to_date

2. To_Date falls in between and From Date is earlier

table.start_date > from_date and table.end_date BETWEEN from_date AND to_date

3. From Date falls in between and To_Date is Later

table.end_date < from_date and table.start_date BETWEEN from_date AND to_date

4. And dates fall with in the range

table.start_date > from_date and table.end_date < to_date

Actually we could have combined this with step 1

Che cosa pensate?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 14:56:34

I thought the question made sense, but that's just me!

Easiest way:

select * from table where
table.start_date < to_Date and
table.end_Date > From_Date

- Jeff

Edited by - jsmith8858 on 07/03/2003 15:16:31

scozzese
Starting Member

12 Posts

Posted - 2003-07-04 : 03:36:34

Thanks for help, good job.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 11:47:02

quote:

I thought the question made sense, but that's just me!

Maybe that's why if Justin let's me, I can be SQL Scrub

quote:

Easiest way:

select * from table where
table.start_date < to_Date and
table.end_Date > From_Date

- Jeff

But what about the "overlapping" dates s/he mentions? That doesn't do it. Unless of course s/he didn't mean that.

Brett

8-)

Edited by - x002548 on 07/07/2003 11:49:54

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 12:10:06

quote:

But what about the "overlapping" dates s/he mentions? That doesn't do it. Unless of course s/he didn't mean that.

Can you give an example of overlapping dates that fails?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 12:14:33

quote:

Table:
1/1/2003 to 1/4/2003
Form:
1/2/2003 to 1/5/2003

These dates overlap

It seems like s/he wanted the rows that were exact matches to dates on a form, dates between and dates (why I don't know) that overlap the range, before and after, but not toally outside the range of dates on the form.

Don't ask me...I just work here.....

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-07 : 13:00:35

data:

table.start_date: 1/1/2003
table.end_date: 1/4/2003
from_date: 1/2/2003
to-date: 1/5/2003

criteria:

table.start_date < to_Date and
table.end_Date > From_Date

Let's test it out:

is table.start_date < to_date? YES
is table.end_date > From_Date? YES

works fine.

- Jeff

Edited by - jsmith8858 on 07/07/2003 13:05:50

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 13:14:57

Yes, I guess it does....

Isn't simplicity nice....

Brett

8-)

scozzese
Starting Member

12 Posts

Posted - 2003-07-07 : 14:00:49

I used Jeff's solution that worked perfectly, I got what I want also for overlap situation.
Do you mind Brett? I like simplicity too.
Thanks to all of you.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:04:12

quote:

Do you mind Brett?

Or...Do I have a mind...

A mind is a terrible thing...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:04:29

to waste..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources