locked
Date Average RRS feed

  • Question

  • Good Day,

    I am trying to write a query to help me find the Frequency between dates.
    I am having a hard time figuring out how to minus the first date from the 2nd & the 2nd from the 3rd & so on...  And somehow stop @ the last record so that 39907 won't mess up the Frequency/Average.
    Need to do this in each change of ACCOUNT_NUM.

    I have well over a million records to go through.

    Your suggestions are most appreciated!!

    Thanks!
    Nigel

    See below for example:


    ACCOUNT_NUM INVOICE_DATE DATE_FREQUENCY
    017004524 07-Mar-12 37
    017004524 30-Jan-12 12
    017004524 18-Jan-12 20
    017004524 29-Dec-11 15
    017004524 14-Dec-11 26
    017004524 18-Nov-11 18
    017004524 31-Oct-11 205
    017004524 09-Apr-11 31
    017004524 09-Mar-11 23
    017004524 14-Feb-11 18
    017004524 27-Jan-11 8
    017004524 19-Jan-11 14
    017004524 05-Jan-11 21
    017004524 15-Dec-10 21
    017004524 24-Nov-10 21
    017004524 03-Nov-10 208
    017004524 09-Apr-10 35
    017004524 05-Mar-10 18
    017004524 15-Feb-10 18
    017004524 28-Jan-10 9
    017004524 19-Jan-10 15
    017004524 04-Jan-10 20
    017004524 15-Dec-09 22
    017004524 23-Nov-09 24
    017004524 30-Oct-09 183
    017004524 30-Apr-09 39907
     
    Count Date Records = 26
    SUM(FREQUENCY)/"Count Date Records" = 40




    xNH


    • Edited by Nigel_143 Thursday, May 24, 2012 1:46 AM
    Thursday, May 24, 2012 1:32 AM

Answers

  • SELECT a.account_num, a.invoice_date, a.invoice_date-MAX(b.invoice_date) AS dateDiff
    FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
            ON a.invoice_date > b.invoice_date
    GROUP BY a.account_num, a.invoice_date

    should supply the difference of successive dates.  The last record, not finding any previous date, will return a NULL as dateDiff. You can add an HAVING clause to remove exessive date span:

    HAVING  a.invoice_date-MAX(b.invoice_date)  < 1000

    The query may take a very long time to run (mostly if you have millions of record).

    • Marked as answer by Nigel_143 Tuesday, June 5, 2012 4:06 PM
    Thursday, May 24, 2012 4:58 PM

All replies

  • There may be a convoluted SQL to do this with a subselect to get the max date less than the current record, but I would do this using an ordinary recordset. Sort the data, then loop forward, keeping track of the previous date and the previous AccountNum in local variables, and doing the date math using DateDiff.


    -Tom. Microsoft Access MVP

    Thursday, May 24, 2012 1:57 AM
  • I am not following your terminology.   Your Date_Frequency does not look like 'frequency' but just the number of days from one date to the other.

    What is the 'Frequency/Average'?     

    Thursday, May 24, 2012 1:43 PM
  • Can you post your SQL Statement? I think in MS Access you may be able to just add an IIF condition on Date_Frequency.

    HTH

    Thursday, May 24, 2012 4:34 PM
  • SELECT a.account_num, a.invoice_date, a.invoice_date-MAX(b.invoice_date) AS dateDiff
    FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
            ON a.invoice_date > b.invoice_date
    GROUP BY a.account_num, a.invoice_date

    should supply the difference of successive dates.  The last record, not finding any previous date, will return a NULL as dateDiff. You can add an HAVING clause to remove exessive date span:

    HAVING  a.invoice_date-MAX(b.invoice_date)  < 1000

    The query may take a very long time to run (mostly if you have millions of record).

    • Marked as answer by Nigel_143 Tuesday, June 5, 2012 4:06 PM
    Thursday, May 24, 2012 4:58 PM
  • Thanks for your responses.
    Vanderghast,  in the next day or 2 i will try what you suggested.

    Imran, my sql statement is very straight forward right now.  This was done in Excel.
    But here it is:

    SELECT ACCOUNT.ACCOUNT_NUM, INVOICE.INVOICE_NUM, INVOICE.INVOICE_DATE
    FROM INVOICE INNER JOIN ACCOUNT ON INVOICE.ACCOUNT_NUM = ACCOUNT.ACCOUNT_NUM
    WHERE (((ACCOUNT.ACCOUNT_NUM)="017004524"))
    ORDER BY ACCOUNT.ACCOUNT_NUM, INVOICE.INVOICE_NUM DESC;

    Thanks! Nigel


    • Edited by Nigel_143 Thursday, May 24, 2012 6:32 PM
    Thursday, May 24, 2012 6:31 PM