Divide a a range of dates into intervals starting with the current date or the latest date in the ra

[Daryl]

Could someone please advise if the following is possible.

I have a table of dates not all dates are represented. I would like to group
these dates into regular intervals, perhaps using the GROUP BY statement.

For example the table may hold 1 year of dates, there may be multiple dates
for the one day and there may be in some cases no entry for a particular
day. The interval must always start from the current date, the interval may
be say 20 days. The idea is to divide the data into 20 intervals from the
date the query is run and then count up the entries if the date falls into
the group/interval.

The main issue is - how do you do this always using the current query run
date as the start date for the interval? I guess I would also accept using
the latest date in the data table as the start date.

Any help would be good.

thanks
daryl

 

[KARL DEWEY]

The interval must always start from the current date, the interval may be
say 20 days. The idea is to divide the data into 20 intervals from the date
the query is run and then count up the entries if the date falls into the
group/interval.

I do not follow this. Do you want to split the record set into 20 parts
that are of same time duration or into the same ammount of records?

Post sample data and expexted results.

 

[John Spencer]

Possible, yes.

The 20-day intervals can be calculated using
([TheDateField]-Date())\20

SELECT ([TheDateField]-Date())\20 as IntervalNumber
, Count([TheDateField]) as DateCount
FROM [SomeTable]
GROUP BY ([TheDateField]-Date())\20

If you wanted the start of the interval
DateAdd("D",20*(([TheDateField]-Date())\20),Date())

All of the above is speculation and is untested.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County