Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Is this information possible to uncover via Access?

    Hello,

    I have a dataset with Sub ID's and start/end dates. How can i determine what the average time between orders is? For Example, I have 5 orders under one Sub ID, and I need to determine the time elapsed between the end date of one order and the start date of the next order in a chronological manner. This is easy if you're comparing one order to a min or a max order, but if you're on the 3rd order for that Sub ID, how can you tell it to take the order previous to the one you're on? Is there some easy built in function for this, or would I have to build some deep IIF statement or use VBA?


    Any help would be greatly appreciated. I have looked through the SQL and Access books that I have and I'm not seeing any topics that deal with something of this nature.

    Thanks for reading,

    Eric

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the table has an Identity column, a rather simple query can provide the expected results. Here's an example:

    1. Table Tbl_Orders:
    SysCounter: AutoNumber (Identity), OrderID: Text, StartDate: DateTime, EndDate: DateTime
    Code:
    SysCounter	OrderID	StartDate	EndDate
    1		a1	2012-01-10	2012-01-15
    2		a1	2012-02-11	2012-02-12
    3		a1	2012-02-15	2012-02-18
    4		a2	2012-02-15	2012-02-16
    5		a3	2012-02-17	2012-02-22
    6		a4	2012-03-11	2012-03-18
    7		a1	2012-03-12	2012-03-26
    8		a1	2012-04-22	2012-04-28
    2. This query:
    Code:
    SELECT Tbl_Orders.SysCounter, 
           Tbl_Orders.OrderID, 
           Tbl_Orders.StartDate, 
           Min(DateDiff('d', a.EndDate, Tbl_Orders.StartDate)) AS ElapsedDaysSinceLastOrderEndDate
    FROM  (Tbl_Orders INNER JOIN (SELECT Tbl_Orders.OrderID, 
                                         Tbl_Orders.StartDate, 
                                         Tbl_Orders.EndDate 
                                    FROM Tbl_Orders) AS a 
                      ON (a.EndDate <= Tbl_Orders.StartDate) AND (Tbl_Orders.OrderID = a.OrderID)
          ) 
    GROUP BY Tbl_Orders.SysCounter, 
             Tbl_Orders.OrderID, 
             Tbl_Orders.StartDate
    ORDER BY Tbl_Orders.StartDate;
    Yields:
    Code:
    SysCounter	OrderID	StartDate	ElapsedDaysSinceLastOrderEndDate
    2		a1	2012-02-11	27
    3		a1	2012-02-15	3
    7		a1	2012-03-12	23
    8		a1	2012-04-22	27
    Note: The Identity column is not necessary and this also yields the same results:
    Code:
    SELECT Tbl_Orders.OrderID, Tbl_Orders.StartDate, Min(DateDiff('d', a.EndDate, Tbl_Orders.StartDate)) AS ElapsedDaysSinceLastOrderEndDate
    FROM Tbl_Orders INNER JOIN [SELECT Tbl_Orders.OrderID, 
                                         Tbl_Orders.StartDate, 
                                         Tbl_Orders.EndDate 
                                    FROM Tbl_Orders]. AS a ON (a.EndDate <= Tbl_Orders.StartDate) AND (Tbl_Orders.OrderID = a.OrderID)
    GROUP BY Tbl_Orders.OrderID, Tbl_Orders.StartDate
    ORDER BY Tbl_Orders.StartDate;
    Last edited by Sinndho; 05-22-12 at 05:57. Reason: Correction: Identity condition not mandatory.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    Just wanted to check in and say that this worked perfectly. The secret of it seems to be the min(datediff()) piece, yes?

    Thanks a ton, this is going to help me tremendously.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Note: There are actually two features at work here:
    1. Min function: as it is an aggregate function, a GROUP BY clause is mandatory.
    2. A query joined on a subquery based on the same table with a ranking clause (a.EndDate <= Tbl_Orders.StartDate): this allows the computation of so-called running sums and other related derived values.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •