Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Jay, Nov 10, 2005.

  1. Jay

    Jay
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 9 Nov 2005 10:03:08 -0800, "Jay" <jason.s.moore1@navy.mil> wrote:

    >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.


    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]
     
  4. Jay

    Jay
    Expand Collapse
    Guest

    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
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 9 Nov 2005 13:17:35 -0800, "Jay" <jason.s.moore1@navy.mil> wrote:

    >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


    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]
     
  6. Jay

    Jay
    Expand Collapse
    Guest

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

    James A. Fortune
    Expand Collapse
    Guest

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


    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
     
  8. Smartin

    Smartin
    Expand Collapse
    Guest

    Jay wrote:
    > 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
    >
    >> Yes, I am looking for the busiest three-day period ever. Yeah, I have
    >> no experience with overlapping dates.


    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.

    --
    Smartin
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 09 Nov 2005 14:44:13 -0700, John Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

    >On 9 Nov 2005 13:17:35 -0800, "Jay" <jason.s.moore1@navy.mil> wrote:
    >
    >>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

    >
    >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]


    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]
     
  10. Jay

    Jay
    Expand Collapse
    Guest

    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.
     
  11. Jay

    Jay
    Expand Collapse
    Guest

    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.
     
  12. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 9 Nov 2005 21:54:00 -0800, "Jay" <jason.s.moore1@navy.mil> wrote:

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


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

    John W. Vinson[MVP]
     
  13. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 9 Nov 2005 21:54:00 -0800, "Jay" <jason.s.moore1@navy.mil> wrote:

    >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.


    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]
     
  14. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Jay wrote:
    > 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.
    >


    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
     
  15. Jay

    Jay
    Expand Collapse
    Guest

    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.
     

Share This Page