Finding Multiple Date Ranges

  • Thread starter Thread starter JLJones13
  • Start date Start date
J

JLJones13

I have a table with several columns, two of which are
Originator (text field) and
Date (Date/Time field, format "MM/DD/YYYY HH:MM:SS")
I would like to get consecutive date ranges for each Name.

Example:
Name Start End
John 5/3/2003 5/22/2003
John 5/31/2003 6/18/2003
John 9/1/2003 9/19/2003
Kelly 4/2/2003 4/3/2003
....And so on...

Meaning that John has entries in the table for 5/3, 5/4,....5/22 and then
has entries from 5/31, 6/1... 6/18, etc.

I tried a suggestion I found on this board that was a solution to a similar
problem, but it requires an Autonumber field. I don't own these tables and
therefore I cannot add any fields to the table.

Here's my attempt. However, it only gives one entry for each person
(i.e.John with a start date of 5/3 and end date of 9/19).

SELECT
Min(R.Date) AS start,
Max(R.Date) AS end,
R.Originator
FROM dbo_V_AuditMailboxMsgsRcvd R
GROUP BY R.Originator
ORDER BY 1;


Any ideas?
 
I think you want a list of all dates between their start
and end.
Use Excel to create a column of dates using the auto-fill.
Copy and paste append into table named DayList, field
DayList.
Put the DayList table in your query unjoined.
Pull down field DayList to the Field line. In the criteria
enter
Between [Start] and [End]
 
JLJones13 said:
I have a table with several columns, two of which are
Originator (text field) and
Date (Date/Time field, format "MM/DD/YYYY HH:MM:SS")
I would like to get consecutive date ranges for each Name.

Example:
Name Start End
John 5/3/2003 5/22/2003
John 5/31/2003 6/18/2003
John 9/1/2003 9/19/2003
Kelly 4/2/2003 4/3/2003
...And so on...

Meaning that John has entries in the table for 5/3, 5/4,....5/22 and then
has entries from 5/31, 6/1... 6/18, etc.
Click to expand...


Why do you need to list 5/4, 5/6 etc.? It is not much better to simply list:

John 5/3/2003 5/22/2003

The above is far nicer to read. I don't understand why, or for what reason
you need a "list". If you are doing a query for names that fall within a
date range, you can simply query your above data with a date range, and only
names that fall within that range will be listed. However, I see no reason
to "list out" 5/4, 5/5 etc?

are you trying to generate this list, and for what?, or are you trying to
build a simply date range query that shows all names that fall within a give
date range?

If you are just looking for ANY name in a date given date range (a
collision)...you certainly don't need to generate a list with date date..

You can use:

select CustName from tblMain
where RequestedStartDate <= EndDateField
and
RequestedEndDate >= StartDateField
 
Thank you for both for your responses. I appreciate your help.

I may have been unclear on exactly what I want, so I want to make sure the
end result is understood.

What I would like to see are consecutive date ranges grouped together. So,
if there is a gap of more than one day, that will start another grouping of
data.

If, in my table, I have:
Mary 5/2
Mary 5/3
Mary 5/4
Mary 5/6

I want the result to be:
Name St End
Mary 5/2 5/4
Mary 5/6 5/6

In a nutshell, the reason I want to get specific date ranges is to place
them in a list box.

Now for the long explanation...
We have a program that reads Exchange log files. With this program we can
select a certain domain or mailbox to audit. The auditing will not be left
on for very long as it adds a lot of records to the table, however, one
domain or mailbox may be audited during several different time frames.

So, instead of using an interface that indicates a mailbox (i.e. John) has
audit data from 5/3 - 9/18, I want a drop down box that shows that we have
audit data on John from 5/3 - 5/22, 5/31 - 6/18, etc. This way, when the
user chooses to view overall server performance and inbound/outbound mail
stats for 7/22 - 9/22, for example, he will see that past data is available
for John and can choose a specific date range to view instead of choosing to
audit John again and adding to the overhead on the tables...

I hope this explanation is clear enough. Once I typed it all out, it
sounded rather complicated for something simple :o) If you see a different
way of presenting this data, that would be great, too!
 
This addresses only the SQL needed to show the range of dates, not a better
way to present your data.

Assume the table is called tblDateRanges (Person, dtDate)
If you modify the queries below, and you have a field called "name", put
brackets around "[name]".

Call this query qDateRangeMax:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",-1,[R].[dtDate])));

Call this query qDateRangeEnd:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",1,[R].[dtDate])));

Call this query qDateRangeMin:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",1,[R].[dtDate])));

Call this query qDateRangeStart:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges LEFT JOIN qDateRangeMin ON (tblDateRanges.dtDate =
qDateRangeMin.dtDate) AND (tblDateRanges.Person = qDateRangeMin.Person)
WHERE (((qDateRangeMin.dtDate) Is Null));

Then this query will show the ranges:
SELECT qDateRangeStart.Person, qDateRangeStart.dtDate AS Start, (select
min(E.dtDate) from qDateRangeEnd as E where E.dtDate>=qDateRangeStart.dtDate
and E.Person=qDateRangeStart.Person) AS End
FROM qDateRangeStart;
 
Thank you for the queries, John! They're perfect!

It took me a little while to respond because I was fiddling with the dates
(I ended up formatting as short date) and then I changed the MIN to MAX in
the last query to get the layout I was looking for.

You've saved me time and I learned something along the way :o)

Thanks!

John Verhagen said:
This addresses only the SQL needed to show the range of dates, not a better
way to present your data.

Assume the table is called tblDateRanges (Person, dtDate)
If you modify the queries below, and you have a field called "name", put
brackets around "[name]".

Call this query qDateRangeMax:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",-1,[R].[dtDate])));

Call this query qDateRangeEnd:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",1,[R].[dtDate])));

Call this query qDateRangeMin:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges, tblDateRanges AS R
WHERE (((tblDateRanges.Person)=[R].[Person]) AND
((tblDateRanges.dtDate)=DateAdd("d",1,[R].[dtDate])));

Call this query qDateRangeStart:
SELECT tblDateRanges.Person, tblDateRanges.dtDate
FROM tblDateRanges LEFT JOIN qDateRangeMin ON (tblDateRanges.dtDate =
qDateRangeMin.dtDate) AND (tblDateRanges.Person = qDateRangeMin.Person)
WHERE (((qDateRangeMin.dtDate) Is Null));

Then this query will show the ranges:
SELECT qDateRangeStart.Person, qDateRangeStart.dtDate AS Start, (select
min(E.dtDate) from qDateRangeEnd as E where E.dtDate>=qDateRangeStart.dtDate
and E.Person=qDateRangeStart.Person) AS End
FROM qDateRangeStart;

JLJones13 said:
Thank you for both for your responses. I appreciate your help.

I may have been unclear on exactly what I want, so I want to make sure the
end result is understood.

What I would like to see are consecutive date ranges grouped together. So,
if there is a gap of more than one day, that will start another grouping of
data.

If, in my table, I have:
Mary 5/2
Mary 5/3
Mary 5/4
Mary 5/6

I want the result to be:
Name St End
Mary 5/2 5/4
Mary 5/6 5/6

In a nutshell, the reason I want to get specific date ranges is to place
them in a list box.

Now for the long explanation...
We have a program that reads Exchange log files. With this program we can
select a certain domain or mailbox to audit. The auditing will not be left
on for very long as it adds a lot of records to the table, however, one
domain or mailbox may be audited during several different time frames.

So, instead of using an interface that indicates a mailbox (i.e. John) has
audit data from 5/3 - 9/18, I want a drop down box that shows that we have
audit data on John from 5/3 - 5/22, 5/31 - 6/18, etc. This way, when the
user chooses to view overall server performance and inbound/outbound mail
stats for 7/22 - 9/22, for example, he will see that past data is available
for John and can choose a specific date range to view instead of
Click to expand...
choosing
to
audit John again and adding to the overhead on the tables...

I hope this explanation is clear enough. Once I typed it all out, it
sounded rather complicated for something simple :o) If you see a different
way of presenting this data, that would be great, too!


and
only a
give
Click to expand...
Click to expand...
 
Back
Top