Overlapping timeframe

G

Guest

I am trying to run a query that will show all records that are active during
a specific timeframe. I would like to be prompted to enter the begin and end
of the timeframe, then see any records that are "open" during that time.

Example:
I would enter 6/1/05 01:00 for the StartDate and 6/1/05 02:00 for the
EndDate, to see the following list:

StartDate EndDate CR_ID
6/1/05 00:00 6/1/05 06:00 200505001
6/1/05 00:00 6/1/05 02:00 200505004
6/1/05 00:01 6/1/05 06:00 200505010
6/1/05 01:00 6/1/05 02:00 200505030

It should be a parameter query.

This is what I've tried based on other posts, but not getting the desired
results:

SELECT a.PLANNED_START_DATE AS aStartDate, a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a INNER JOIN ACTIVITY_DATES AS b ON (a.CR_ID =
b.CR_ID) AND (a.PLANNED_START_DATE = b.PLANNED_START_DATE)
WHERE (((a.PLANNED_START_DATE)<=.[PLANNED_START_DATE] And
(a.PLANNED_START_DATE) Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)>=.[PLANNED_START_DATE])) OR
(((a.PLANNED_START_DATE)>=.[PLANNED_START_DATE] And (a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)<=.[PLANNED_END_DATE])) OR
(((a.PLANNED_START_DATE)<=.[PLANNED_END_DATE] And (a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)>=.[PLANNED_END_DATE]))
ORDER BY a.PLANNED_START_DATE, b.PLANNED_END_DATE
WITH OWNERACCESS OPTION;

Thank you, Mary
 
J

JR

Mary

I think your start date should be the limit of the date range you want to
look at. For example, if you're interested in the records active between
20050101 and 20050630 you would structure the query like this:

SELECT a.PLANNED_START_DATE AS aStartDate,
a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a
WHERE a.PLANNED_START_DATE < "20050701"
AND a.PLANNED_END_DATE > "20041231"

You would adjust the date format to suit your requirements, I have just used
the YYYYMMDD format because it's universal and easy to understand.

I haven't tested this, and I hope it is what you meant. You could try it.

JR
 
G

Guest

Thanks for trying, but I'm still not getting it. It has to be a parameter
query that prompts for the two dates.

JR said:
Mary

I think your start date should be the limit of the date range you want to
look at. For example, if you're interested in the records active between
20050101 and 20050630 you would structure the query like this:

SELECT a.PLANNED_START_DATE AS aStartDate,
a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a
WHERE a.PLANNED_START_DATE < "20050701"
AND a.PLANNED_END_DATE > "20041231"

You would adjust the date format to suit your requirements, I have just used
the YYYYMMDD format because it's universal and easy to understand.

I haven't tested this, and I hope it is what you meant. You could try it.

JR

Mary said:
I am trying to run a query that will show all records that are active
during
a specific timeframe. I would like to be prompted to enter the begin and
end
of the timeframe, then see any records that are "open" during that time.

Example:
I would enter 6/1/05 01:00 for the StartDate and 6/1/05 02:00 for the
EndDate, to see the following list:

StartDate EndDate CR_ID
6/1/05 00:00 6/1/05 06:00 200505001
6/1/05 00:00 6/1/05 02:00 200505004
6/1/05 00:01 6/1/05 06:00 200505010
6/1/05 01:00 6/1/05 02:00 200505030

It should be a parameter query.

This is what I've tried based on other posts, but not getting the desired
results:

SELECT a.PLANNED_START_DATE AS aStartDate, a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a INNER JOIN ACTIVITY_DATES AS b ON (a.CR_ID =
b.CR_ID) AND (a.PLANNED_START_DATE = b.PLANNED_START_DATE)
WHERE (((a.PLANNED_START_DATE)<=.[PLANNED_START_DATE] And
(a.PLANNED_START_DATE) Between [Enter Start Date] And [Enter End Date])
AND
((a.PLANNED_END_DATE)>=.[PLANNED_START_DATE])) OR
(((a.PLANNED_START_DATE)>=.[PLANNED_START_DATE] And
(a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)<=.[PLANNED_END_DATE])) OR
(((a.PLANNED_START_DATE)<=.[PLANNED_END_DATE] And
(a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)>=.[PLANNED_END_DATE]))
ORDER BY a.PLANNED_START_DATE, b.PLANNED_END_DATE
WITH OWNERACCESS OPTION;

Thank you, Mary
Click to expand...
Click to expand...
 
J

JR

Hi Mary,

Yes sorry I did leave that out. I figured it was just the date logic you
were interested in.

SELECT a.PLANNED_START_DATE AS aStartDate,
a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a
WHERE a.PLANNED_START_DATE < [Enter Start Date]
AND a.PLANNED_END_DATE > [Enter End Date]

All you do is add the bracketed text to get the query to prompt you for the
parameters.

I hope this helps.

JR
Mary said:
Thanks for trying, but I'm still not getting it. It has to be a parameter
query that prompts for the two dates.

JR said:
Mary

I think your start date should be the limit of the date range you want to
look at. For example, if you're interested in the records active between
20050101 and 20050630 you would structure the query like this:

SELECT a.PLANNED_START_DATE AS aStartDate,
a.PLANNED_END_DATE AS aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a
WHERE a.PLANNED_START_DATE < "20050701"
AND a.PLANNED_END_DATE > "20041231"

You would adjust the date format to suit your requirements, I have just
used
the YYYYMMDD format because it's universal and easy to understand.

I haven't tested this, and I hope it is what you meant. You could try
it.

JR

Mary said:
I am trying to run a query that will show all records that are active
during
a specific timeframe. I would like to be prompted to enter the begin
and
end
of the timeframe, then see any records that are "open" during that
time.

Example:
I would enter 6/1/05 01:00 for the StartDate and 6/1/05 02:00 for the
EndDate, to see the following list:

StartDate EndDate CR_ID
6/1/05 00:00 6/1/05 06:00 200505001
6/1/05 00:00 6/1/05 02:00 200505004
6/1/05 00:01 6/1/05 06:00 200505010
6/1/05 01:00 6/1/05 02:00 200505030

It should be a parameter query.

This is what I've tried based on other posts, but not getting the
desired
results:

SELECT a.PLANNED_START_DATE AS aStartDate, a.PLANNED_END_DATE AS
aEndDate,
a.CR_ID AS aCR_ID
FROM ACTIVITY_DATES AS a INNER JOIN ACTIVITY_DATES AS b ON (a.CR_ID =
b.CR_ID) AND (a.PLANNED_START_DATE = b.PLANNED_START_DATE)
WHERE (((a.PLANNED_START_DATE)<=.[PLANNED_START_DATE] And
(a.PLANNED_START_DATE) Between [Enter Start Date] And [Enter End Date])
AND
((a.PLANNED_END_DATE)>=.[PLANNED_START_DATE])) OR
(((a.PLANNED_START_DATE)>=.[PLANNED_START_DATE] And
(a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)<=.[PLANNED_END_DATE])) OR
(((a.PLANNED_START_DATE)<=.[PLANNED_END_DATE] And
(a.PLANNED_START_DATE)
Between [Enter Start Date] And [Enter End Date]) AND
((a.PLANNED_END_DATE)>=.[PLANNED_END_DATE]))
ORDER BY a.PLANNED_START_DATE, b.PLANNED_END_DATE
WITH OWNERACCESS OPTION;

Thank you, Mary
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to enter start and end date/time in excel based on some specific value. 7
How to interpret CPUZ spd readouts? 5
Unmatch return query 5
More help needed for previous query 6
Delay counting 1
RANK 1
Employee Availblility 3
Help with a troublesome Query - Think it needs embeded SubQueries 5

Top