Prev: move data
Next: The performance counter registry hive is corrupted
From: mclaugh2010 on 17 Jun 2010 11:26
I have a table that has several columns, two of which are date fields. A
start_date and comp_date for start and completion date.

I need to run a query that will return all the records between two dates
selected by a user. Not hard, piece of cake. The problem I am having is that
my query isn't returning all the records that overlap a given date range.

For example:

I select a date range: June 1 to September 1.
My query will return 10 records that start and stop between June 1 and
September 1. This is good and correct, but....

...I have several records that start before June 1 and end between June 1 and
September 1.
I also have several records that start after June 1 and end after September 1.


These few records that overlap my entered date range should also show, but
they do not.

How do I write a query that will return not just the records that start and
stop within my date range, but will also return any records that may overlap
my date range?

From: Iain Sharp on 17 Jun 2010 11:54
On Thu, 17 Jun 2010 15:26:14 GMT, "mclaugh2010" <u61028(a)uwe> wrote:

>I have a table that has several columns, two of which are date fields. A
>start_date and comp_date for start and completion date.
>
>I need to run a query that will return all the records between two dates
>selected by a user. Not hard, piece of cake. The problem I am having is that
>my query isn't returning all the records that overlap a given date range.
>
>For example:
>
>I select a date range: June 1 to September 1.
>My query will return 10 records that start and stop between June 1 and
>September 1. This is good and correct, but....
>
>..I have several records that start before June 1 and end between June 1 and
>September 1.
>I also have several records that start after June 1 and end after September 1.
>
>
>These few records that overlap my entered date range should also show, but
>they do not.
>
>How do I write a query that will return not just the records that start and
>stop within my date range, but will also return any records that may overlap
>my date range?

Assuming comp_date must be >= start_date, try the following.

where
table.start_date <= user_end_date and table.comp_date >=
user_start_date

Which gets everything which started before the end of the period, and
ended after the start of the period.

This will find items which started before the period and ended after
it, i.e. were ongoing during the period.

Iain
From: b. edwards on 17 Jun 2010 18:54
On 6/17/2010 10:26 AM, mclaugh2010 wrote:
> I have a table that has several columns, two of which are date fields. A
> start_date and comp_date for start and completion date.
>
> I need to run a query that will return all the records between two dates
> selected by a user. Not hard, piece of cake. The problem I am having is that
> my query isn't returning all the records that overlap a given date range.
>
> For example:
>
> I select a date range: June 1 to September 1.
> My query will return 10 records that start and stop between June 1 and
> September 1. This is good and correct, but....
>
> ..I have several records that start before June 1 and end between June 1 and
> September 1.
> I also have several records that start after June 1 and end after September 1.
>
>
> These few records that overlap my entered date range should also show, but
> they do not.
>
> How do I write a query that will return not just the records that start and
> stop within my date range, but will also return any records that may overlap
> my date range?
>

You mean something like:
SELECT * FROM table as t
WHERE t.StartDate BETWEEN (@StartDate, @EndDate)
OR t.EndDate BETWEEN (@StartDate, @EndDate)


 | 
Pages: 1
Prev: move data
Next: The performance counter registry hive is corrupted