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

  • Thread starter Thread starter Jay
  • Start date Start date
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.
 
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]
 
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
 
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]
 
Yes, I am looking for the busiest three-day period ever. Yeah, I have
no experience with overlapping dates.
 
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
 
Jay said:
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...

Off the top of head...

I don't know how this could be done with straight SQL but perhaps the
following would work with some VBA...

Build a RecordSet containing all Dategroup values in sorted order
and Sum values
Walk through the Recordset until EOF
Save First, Second, Third Sum values in variables
Determine Max(First, Second, Third)
Return Max and corresponding Dategroup

This would return the max order in each contiguous triplet of Dategroup.

You will probably run into problems because the Dategroup field will not
likely sort nicely. It would be much easier if Dategroup was split into
"Start" and "End" dates. This could probably be done on the fly by
parsing the Dategroup as you build the ResordSet.
 
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]
 
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.
 
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.
 
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]
 
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]
 
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
 
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.
 
Back
Top