Consecutive Query

[NeonSky via AccessMonster.com]

Good Day,

For your consideration I have the following question.

I have a process that leads to the below dataset.

Name Start Date End Date
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/8/2008
Joe 11/8/2008 11/22/2008
Joe 11/22/2008 11/29/2008
Joe 12/10/2008 12/17/2008
Joe 12/17/2008 12/24/2008

Now that I have this dataset I would like to only include Arrival
Dates that begin on a day within a 6 day range. In this example 11/1/2008
(start date) to 11/7/2008 (end date) (this "start date" and "end date" would
be defined by the [ENTER DATE] command in the criteria of the two respective
fields). Now here is the tricky part,
I would also like to include those reservations that are consecutive where
they begin from a start date within the specified range. Thus my final
dataset would look like below....

Name Start Date End Date
Chris 11/2/2008 11/9/2008
Joe 11/1/2008 11/8/2008
Joe 11/8/2008 11/22/2008
Joe 11/22/2008 11/29/2008

Thank you for your time and thoughts!

 

[John Spencer]

I suspect you need a query that looks something like the following

SELECT YourTable.[Name], [Start Date], [End Date]
FROM YourTable
WHERE [Start Date] Between #11/1/2008# and #11/7/2008#
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM YourTable as Tmp
WHERE Tmp.[Start date] Between #11/1/2008# and #11/7/2008#
AND tmp.[Name] = YourTable.[Name] )


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[NeonSky via AccessMonster.com]

Hello John,

Thank you for your response. Though the query you outlined below is pulling
back all my rows regardless of whether or not the "Start Date" falls within
the specified range. Though please keep in mind that the returns are not
limited soley to those reservations where start date falls within the
specified range. Yes those reservations are to be included, though please
consider the following logic which I believe encompasses what we are trying
to do.....where start date falls within specified range AND start date is
equal to end date of records where start date is within specified range AND
start date is equal to end date of records that meet the previous criteria....
and so on and so on until no "consecutive" relationship exists....my initial
example illustrates this concept...I am just not sure how to do it.....Here
is my code as of now....Thank you for your time!

SELECT tblTempAD.[Name], [Start Date], [End Date]
FROM tblTempAD
WHERE [Start Date].[ENTER DATE] Between [Start Date].[ENTER DATE] AND [End
Date].[ENTER DATE]
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM tblTempAD as Tmp
WHERE Tmp.[Start Date] BETWEEN [Start Date].[ENTER DATE] AND [End Date].
[ENTER DATE]
AND tmp.[Name] = tblTempAD.[Name] );

I suspect you need a query that looks something like the following

SELECT YourTable.[Name], [Start Date], [End Date]
FROM YourTable
WHERE [Start Date] Between #11/1/2008# and #11/7/2008#
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM YourTable as Tmp
WHERE Tmp.[Start date] Between #11/1/2008# and #11/7/2008#
AND tmp.[Name] = YourTable.[Name] )

Good Day,

[quoted text clipped - 28 lines]

Thank you for your time and thoughts!

 

[Michel Walsh]

Seems a rather complex task.

First, I would make a query that merge the sequences:

Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008
Joe 12/10/2008 12/24/2008


From these, I would pick the intervals which overlap (in part) the [enter
start date] and [enter end date]

parameters: 11/1/2008 and 11/7/2008
result::
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008


And last, I would pump the original rows which overlap (in part) the last
result.




So, the details. If you don't mind, I call the fields Name, ArrivalDate and
DepartDate; I call the initial table Table2 (because I already have the SQL
statements for that job, but with those names )




Define the query Arrivals:
--------------------
SELECT a.Name, a.ArrivalDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.ArrivalDate = b.DepartDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));
------------------------

Define the query Departures:
------------------------
SELECT a.Name, a.DepartDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));
-----------------------


We will obtain the merged sequences, ArrivalsDepartures, with:
------------------------
SELECT Arrivals.Name, Arrivals.ArrivalDate AS Arrival,
Min(Departures.DepartDate) AS Departure
FROM Arrivals INNER JOIN Departures ON
(Arrivals.ArrivalDate<=departures.departDate) AND
(Arrivals.Name=Departures.Name)
GROUP BY Arrivals.Name, Arrivals.ArrivalDate;
-----------------------



That should result into:

Name Arrival Departure
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008
Joe 12/10/2008 12/24/2008




Now, (from here I haven't checked, it is just air code) to get partial
overlap over the interval [paramStart] and [paramEnd]:

query: InvolvedIntervals
-------------------------
SELECT Name, Arrival, Departure
FROM ArrivalsDepartures
WHERE Arrival<= paramEnd AND Departure >= paramStart
-------------------------


And last, find any partial overlap between these intervals and the initial
ones:

-------------------------
SELECT table2.*
FROM table2 INNER JOIN InvolvedIntervals AS v
ON table2.Name = v.Name
AND table2.ArrivalDate <= v.Departure
AND table2.DepartDate >= v.Arrival
-------------------------





Hoping it may help,
Vanderghast, Access MVP

Good Day,

For your consideration I have the following question.

I have a process that leads to the below dataset.

Name Start Date End Date
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/8/2008
Joe 11/8/2008 11/22/2008
Joe 11/22/2008 11/29/2008
Joe 12/10/2008 12/17/2008
Joe 12/17/2008 12/24/2008

Now that I have this dataset I would like to only include Arrival
Dates that begin on a day within a 6 day range. In this example 11/1/2008
(start date) to 11/7/2008 (end date) (this "start date" and "end date"
would
be defined by the [ENTER DATE] command in the criteria of the two
respective
fields). Now here is the tricky part,
I would also like to include those reservations that are consecutive where
they begin from a start date within the specified range. Thus my final
dataset would look like below....

Name Start Date End Date
Chris 11/2/2008 11/9/2008
Joe 11/1/2008 11/8/2008
Joe 11/8/2008 11/22/2008
Joe 11/22/2008 11/29/2008

Thank you for your time and thoughts!

 

[John Spencer]

TRY the following.
Parameters [Start Date] DateTime;
SELECT tblTempAD.[Name], [Start Date], [End Date]
FROM tblTempAD
WHERE [ENTER DATE] Between [Start Date] AND [End Date]
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM tblTempAD as Tmp
WHERE [ENTER Date] BETWEEN [Start Date] AND [End Date]
AND tmp.[Name] = tblTempAD.[Name] );

If you are willing to put in a date range for the period then
Parameters [Period begins on] DateTime, [Period Ends On] DateTime;
SELECT tblTempAD.[Name], [Start Date], [End Date]
FROM tblTempAD
WHERE [Start Date] Between [Period Begins on] AND
[Period ends on]
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM tblTempAD as Tmp
WHERE Tmp.[Start Date] BETWEEN [Period Begins on]
AND [Period Ends on]
AND tmp.[Name] = tblTempAD.[Name] );

Your query could be modified to the following
SELECT tblTempAD.[Name], [Start Date], [End Date]
FROM tblTempAD
WHERE [Start Date] Between [Start Date].[ENTER DATE]
AND [End Date].[ENTER DATE]
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM tblTempAD as Tmp
WHERE Tmp.[Start Date] BETWEEN [Start Date].[ENTER DATE]
AND [End Date].[ENTER DATE]
AND tmp.[Name] = tblTempAD.[Name] );

The reason you were getting ALL records was your where condition line that
read

WHERE [Start Date].[ENTER DATE] Between [Start Date].[ENTER DATE]
AND [End Date].[ENTER DATE]

That would always return true since
[Start Date].[ENTER DATE] is always going to equal [Start Date].[ENTER
DATE]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello John,

Thank you for your response. Though the query you outlined below is
pulling
back all my rows regardless of whether or not the "Start Date" falls
within
the specified range. Though please keep in mind that the returns are not
limited soley to those reservations where start date falls within the
specified range. Yes those reservations are to be included, though please
consider the following logic which I believe encompasses what we are
trying
to do.....where start date falls within specified range AND start date is
equal to end date of records where start date is within specified range
AND
start date is equal to end date of records that meet the previous
criteria....
and so on and so on until no "consecutive" relationship exists....my
initial
example illustrates this concept...I am just not sure how to do
it.....Here
is my code as of now....Thank you for your time!

SELECT tblTempAD.[Name], [Start Date], [End Date]
FROM tblTempAD
WHERE [Start Date].[ENTER DATE] Between [Start Date].[ENTER DATE] AND [End
Date].[ENTER DATE]
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM tblTempAD as Tmp
WHERE Tmp.[Start Date] BETWEEN [Start Date].[ENTER DATE] AND [End Date].
[ENTER DATE]
AND tmp.[Name] = tblTempAD.[Name] );

I suspect you need a query that looks something like the following

SELECT YourTable.[Name], [Start Date], [End Date]
FROM YourTable
WHERE [Start Date] Between #11/1/2008# and #11/7/2008#
OR [Start Date] In
(
SELECT tmp.[End Date]
FROM YourTable as Tmp
WHERE Tmp.[Start date] Between #11/1/2008# and #11/7/2008#
AND tmp.[Name] = YourTable.[Name] )

Good Day,

[quoted text clipped - 28 lines]

Thank you for your time and thoughts!

 

[NeonSky via AccessMonster.com]

Hello Michel, Happy new year, glad (relieved!) to see you posting in my
thread. As you can see it is along the same lines of what you so graciously
helped me with in December. I have not had a chance to apply your input to my
project as I have been taken by some recent issues. I just did not want too
much time going by without recognizing your input, and thanking you for it. I
will take a close look at it sometime soon (early next week). Thanks again!

Seems a rather complex task.

First, I would make a query that merge the sequences:

Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008
Joe 12/10/2008 12/24/2008

From these, I would pick the intervals which overlap (in part) the [enter
start date] and [enter end date]

parameters: 11/1/2008 and 11/7/2008
result::
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008

And last, I would pump the original rows which overlap (in part) the last
result.

So, the details. If you don't mind, I call the fields Name, ArrivalDate and
DepartDate; I call the initial table Table2 (because I already have the SQL
statements for that job, but with those names )

Define the query Arrivals:
--------------------
SELECT a.Name, a.ArrivalDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.ArrivalDate = b.DepartDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));
------------------------

Define the query Departures:
------------------------
SELECT a.Name, a.DepartDate
FROM Table2 AS a LEFT JOIN Table2 AS b ON (a.DepartDate = b.ArrivalDate) AND
(a.Name = b.Name)
WHERE (((b.Name) Is Null));
-----------------------

We will obtain the merged sequences, ArrivalsDepartures, with:
------------------------
SELECT Arrivals.Name, Arrivals.ArrivalDate AS Arrival,
Min(Departures.DepartDate) AS Departure
FROM Arrivals INNER JOIN Departures ON
(Arrivals.ArrivalDate<=departures.departDate) AND
(Arrivals.Name=Departures.Name)
GROUP BY Arrivals.Name, Arrivals.ArrivalDate;
-----------------------

That should result into:

Name Arrival Departure
Chris 11/2/2008 11/9/2009
Joe 11/1/2008 11/29/2008
Joe 12/10/2008 12/24/2008

Now, (from here I haven't checked, it is just air code) to get partial
overlap over the interval [paramStart] and [paramEnd]:

query: InvolvedIntervals
-------------------------
SELECT Name, Arrival, Departure
FROM ArrivalsDepartures
WHERE Arrival<= paramEnd AND Departure >= paramStart
-------------------------

And last, find any partial overlap between these intervals and the initial
ones:

-------------------------
SELECT table2.*
FROM table2 INNER JOIN InvolvedIntervals AS v
ON table2.Name = v.Name
AND table2.ArrivalDate <= v.Departure
AND table2.DepartDate >= v.Arrival
-------------------------

Hoping it may help,
Vanderghast, Access MVP

Good Day,

[quoted text clipped - 28 lines]

Thank you for your time and thoughts!