Getting max number of orders for a period of "x" days

J

Jay

Maybe someone could help me with a problem I'm having.
I would like to create a query that returns the max number of orders
processed for a period of 3 days. I say 3 days, but it would actually
be inputted from a form, so it could be any number of days. I already
know how to use that input in an expression, but I don't have a clue as
to how to build the query. I already have a query that pulls sum of
orders processed by day, so if there is someone out there that could
help me, I would appreciate it.
 
J

John Vinson

Maybe someone could help me with a problem I'm having.
I would like to create a query that returns the max number of orders
processed for a period of 3 days. I say 3 days, but it would actually
be inputted from a form, so it could be any number of days. I already
know how to use that input in an expression, but I don't have a clue as
to how to build the query. I already have a query that pulls sum of
orders processed by day, so if there is someone out there that could
help me, I would appreciate it.
Click to expand...

You'll have to explain. Some PARTICULAR three days, such as the
preceding three days? Any three-day period this past year? Overlapping
or not? What's your table structure?

John W. Vinson[MVP]
 
J

Jay

The way the query is designed, it pulls the sum of orders for each date
that there was an order. I need to find what the max amount of orders
was for overlapping three-day periods, I will give an example:
1/1/2005 - 1/3/2005 6
1/2/2005 - 1/4/2005 10
1/3/2005 - 1/5/2005 4
so 10 would be my max number. I want the query to show it like so:
Dategroup Sum of itm_proc
1/2/2005 - 1/4/2005 10
 
J

John Vinson

The way the query is designed, it pulls the sum of orders for each date
that there was an order. I need to find what the max amount of orders
was for overlapping three-day periods, I will give an example:
1/1/2005 - 1/3/2005 6
1/2/2005 - 1/4/2005 10
1/3/2005 - 1/5/2005 4
so 10 would be my max number. I want the query to show it like so:
Dategroup Sum of itm_proc
1/2/2005 - 1/4/2005 10
Click to expand...

Again...

What if you have records in your table with the number of orders from
every day between 3/12/2001 and 11/8/2005? Do you want to see the
busiest three-day period ever, or do you want to limit the range of
dates in any way?

This will be tricky, due to the overlapping. I'll think about it -
anyone else want to jump in?

John W. Vinson[MVP]
 
J

Jay

Yes, I am looking for the busiest three-day period ever. Yeah, I have
no experience with overlapping dates.
 
J

James A. Fortune

Jay said:
Yes, I am looking for the busiest three-day period ever. Yeah, I have
no experience with overlapping dates.
Click to expand...

I'll accept John's invitation.

To simulate your query I created:

tblNumberOfOrders
OID OrderDate NumberOfOrders
1 1/2/05 5
2 1/3/05 3
3 1/4/05 2
4 1/5/05 3
5 1/6/05 2

qryNumberOfOrders:
SELECT OrderDate, NumberOfOrders FROM tblNumberOfOrders ORDER BY OrderDate;

!qryNumberOfOrders:
OrderDate NumberOfOrders
1/2/05 5
1/3/05 3
1/4/05 2
1/5/05 3
1/6/05 2

Note: The following query can use a tblNumberOfOrders created via a make
table query instead of qryNumberOfOrders if efficiency becomes an issue:

qryPeriodOrders:
PARAMETERS N Long;
SELECT Format(qryNumberOfOrders.OrderDate,'m/d/yyyy') & ' - ' &
Format(DateAdd('d',N-1,qryNumberOfOrders.OrderDate),'m/d/yyyy') AS
DateGroup, (SELECT Sum(A.NumberOfOrders) FROM qryNumberOfOrders AS A
WHERE A.OrderDate Between qryNumberOfOrders.OrderDate AND DateAdd('d', N
- 1, qryNumberOfOrders.OrderDate)) AS PeriodOrders FROM qryNumberOfOrders;

!qryPeriodOrders:
N
3

DateGroup PeriodOrders
1/2/2005 - 1/4/2005 10
1/3/2005 - 1/5/2005 8
1/4/2005 - 1/6/2005 7
1/5/2005 - 1/7/2005 5
1/6/2005 - 1/8/2005 2

qryGetMaxPeriodOrders:
SELECT DateGroup, PeriodOrders AS [Sum of itm_proc] FROM qryPeriodOrders
WHERE PeriodOrders = (SELECT MAX(A.PeriodOrders) FROM qryPeriodOrders AS A);

!qryGetMaxPeriodOrders:
N
3

DateGroup Sum of itm_proc
1/2/2005 - 1/4/2005 10

I also tried adding to tblNumberOfOrders
OID OrderDate NumberOfOrders
6 1/7/05 5

to force a tie and got:

DateGroup Sum of itm_proc
1/2/2005 - 1/4/2005 10
1/5/2005 - 1/7/2005 10

Then with N = 4:
DateGroup Sum of itm_proc
1/2/2005 - 1/5/2005 13

This should give you a start. Post back if you need something better.

James A. Fortune
 
J

John Vinson

Again...

What if you have records in your table with the number of orders from
every day between 3/12/2001 and 11/8/2005? Do you want to see the
busiest three-day period ever, or do you want to limit the range of
dates in any way?

This will be tricky, due to the overlapping. I'll think about it -
anyone else want to jump in?

John W. Vinson[MVP]
Click to expand...

Well, I think James may have it - but let me try too. Assuming a table
named Orders with fields OrderDate and OrderCount, one record per day:

qryGroup

SELECT TOP 1 Format([OrderDate], "m/d/yyyy") & "-" &
Format(DateAdd("d", 3, [OrderDate]), "m/d/yyyy") AS DateRange,
DSum("[OrderCount]", "[Orders]", "[OrderDate] >= #" & [OrderDate] & "#
AND OrderDate <= #" & [OrderDate] & "#") ORDER BY DSum("[OrderCount]",
"[Orders]", "[OrderDate] >= #" & [OrderDate] & "# AND OrderDate <= #"
& [OrderDate] & "#");

John W. Vinson[MVP]
 
J

Jay

Hey thanks a lot James, I am going to try it out and I will let you
know how it works out. Thanks for the effort.
 
J

Jay

Hello all,
Well, I haven't gotten around to trying your solution John, but I
will try tomorrow. I appreciate everyone's help on this. James,
your's works great but it takes a little while to run. I haven't set
my filters yet, though, so hopefully that will speed it up a little. I
really appreciate it.
 
J

John Vinson

James,
your's works great but it takes a little while to run.
Click to expand...

Oh, mine will be slower than molasses: sorting by the calculated
concatenated field will be brutal.

John W. Vinson[MVP]
 
J

John Vinson

Hello all,
Well, I haven't gotten around to trying your solution John, but I
will try tomorrow. I appreciate everyone's help on this. James,
your's works great but it takes a little while to run. I haven't set
my filters yet, though, so hopefully that will speed it up a little. I
really appreciate it.
Click to expand...

OOPS.

Mine had an egregious error as well (and correcting it will make it
slower yet).

Should be:

SELECT TOP 1 Format([OrderDate], "m/d/yyyy") & "-" &
Format(DateAdd("d", 3, [OrderDate]), "m/d/yyyy") AS DateRange,
DSum("[OrderCount]", "[Orders]", "[OrderDate] >= #" & [OrderDate] & "#
AND OrderDate <= #" & DateAdd("d", 3, [OrderDate]) & "#") ORDER BY
DSum("[OrderCount]", "[Orders]", "[OrderDate] >= #" & [OrderDate] & "#
AND OrderDate <= #" & DateAdd("d", 3, [OrderDate]) & "#");


John W. Vinson[MVP]
 
J

James A. Fortune

Jay said:
Hello all,
Well, I haven't gotten around to trying your solution John, but I
will try tomorrow. I appreciate everyone's help on this. James,
your's works great but it takes a little while to run. I haven't set
my filters yet, though, so hopefully that will speed it up a little. I
really appreciate it.
Click to expand...

I suspected that. First try the Make Table query approach to use a
table instead of your first query. That should speed it up by at least
a factor of 10. Seeing John's solution gave me some ideas for
improvement in addition to the ones I was already considering. Also, be
sure to index any search fields in the newly created table and in the
original table. Using indexes in the auxiliary table(s) and the
original table should make the queries fly. I don't know what you mean
by setting filters.

James A. Fortune
 
J

Jay

Well, I am filtering by a form, as in, I am only looking at certain
dates and orders. For example, I am looking for all orders placed for
a certain part number during an x day time period. I have already
created an input for the parameter that is based off of another query.
I am definitely going to try that make table approach, and we'll see
how well the indexing makes it work. Yeah, I am having problems with
the sort, but when I get that figured out, I will post that as well.
Thanks John, James, I will probably be posting again on Saturday, as
family decided to unexpectedly visit. Looks like our work is cut out
for us.
 
Top