Welcome to SPN

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

Sign Up Now!

Counting Transactions Within a Timeframe

Discussion in 'Information Technology' started by Smartin, Jul 28, 2006.

  1. Smartin

    Smartin
    Expand Collapse
    Guest

    Good Day All,

    In a transaction table that includes a date/time stamp, I am trying to
    determine, for each row, how many rows of the same table fall within a
    specified period of time.

    I have a solution but it involves adding a new column to the table, and
    that is something I cannot do in production.

    For example,

    Table Times Definition
    =======================
    JOINER Number default=0
    ID PK
    T Date/Time


    Sample Data Table Times
    ========================
    JOINER ID T
    ---------------------------------------
    0 1 7/19/2006 4:00:00 PM
    0 2 7/19/2006 4:01:00 PM
    0 3 7/19/2006 4:02:00 PM
    0 4 7/19/2006 10:00:00 PM
    0 5 7/19/2006 10:01:00 PM
    0 6 7/19/2006 2:00:00 PM
    0 8 7/19/2006 4:45:00 PM
    0 9 7/19/2006 6:07:22 PM
    0 10 7/19/2006 6:07:29 PM
    0 11 7/19/2006 5:03:00 PM
    0 12 7/19/2006 5:08:00 PM


    Sample Query
    =============
    SELECT T1.T, Count(T2.ID) AS Transactions
    FROM Times T1
    INNER JOIN Times T2
    ON T1.JOINER = T2.JOINER
    WHERE (ABS(T2.T - T1.T) < (1/24))
    GROUP BY T1.T


    Sample Results
    =====================================
    T Transactions
    7/19/2006 2:00:00 PM 1
    7/19/2006 4:00:00 PM 4
    7/19/2006 4:01:00 PM 4
    7/19/2006 4:02:00 PM 4
    7/19/2006 4:45:00 PM 6
    7/19/2006 5:03:00 PM 3
    7/19/2006 5:08:00 PM 5
    7/19/2006 6:07:22 PM 3
    7/19/2006 6:07:29 PM 3
    7/19/2006 10:00:00 PM 2
    7/19/2006 10:01:00 PM 2

    This is the expected result of all transactions within one hour of each
    row, but I had to add column "JOINER" to the table and populate this
    column with all zeros for something to join every row of the table to
    itself in the subquery.

    Does anyone know of a way to do this without the inclusion of the
    "dummy" column JOINER?


    --
    Smartin
     
  2. Loading...

    Similar Threads Forum Date
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016
    Malaysia Malaysian 2013 Election - Vote Counting Underway Breaking News May 5, 2013
    No Counting Allowed in Gurmatt Jap Ji Sahib Dec 15, 2012
    Christianity Ye Olde King James Version, 400 Years and Counting Interfaith Dialogues Mar 27, 2011
    Will counting caste help to reduce inequality? Hard Talk May 11, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    In actual fact, you don't need that column. Try:

    SELECT T1.T, Count(T2.ID) AS Transactions
    FROM Times T1, Times T2
    WHERE (ABS(T2.T - T1.T) < (1/24))
    GROUP BY T1.T

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Smartin" <smartin108@yahoo.com> wrote in message
    news:C56dnZ4ptoigLCPZnZ2dnUVZ_tGdnZ2d@giganews.com...
    > Good Day All,
    >
    > In a transaction table that includes a date/time stamp, I am trying to
    > determine, for each row, how many rows of the same table fall within a
    > specified period of time.
    >
    > I have a solution but it involves adding a new column to the table, and
    > that is something I cannot do in production.
    >
    > For example,
    >
    > Table Times Definition
    > =======================
    > JOINER Number default=0
    > ID PK
    > T Date/Time
    >
    >
    > Sample Data Table Times
    > ========================
    > JOINER ID T
    > ---------------------------------------
    > 0 1 7/19/2006 4:00:00 PM
    > 0 2 7/19/2006 4:01:00 PM
    > 0 3 7/19/2006 4:02:00 PM
    > 0 4 7/19/2006 10:00:00 PM
    > 0 5 7/19/2006 10:01:00 PM
    > 0 6 7/19/2006 2:00:00 PM
    > 0 8 7/19/2006 4:45:00 PM
    > 0 9 7/19/2006 6:07:22 PM
    > 0 10 7/19/2006 6:07:29 PM
    > 0 11 7/19/2006 5:03:00 PM
    > 0 12 7/19/2006 5:08:00 PM
    >
    >
    > Sample Query
    > =============
    > SELECT T1.T, Count(T2.ID) AS Transactions
    > FROM Times T1
    > INNER JOIN Times T2
    > ON T1.JOINER = T2.JOINER
    > WHERE (ABS(T2.T - T1.T) < (1/24))
    > GROUP BY T1.T
    >
    >
    > Sample Results
    > =====================================
    > T Transactions
    > 7/19/2006 2:00:00 PM 1
    > 7/19/2006 4:00:00 PM 4
    > 7/19/2006 4:01:00 PM 4
    > 7/19/2006 4:02:00 PM 4
    > 7/19/2006 4:45:00 PM 6
    > 7/19/2006 5:03:00 PM 3
    > 7/19/2006 5:08:00 PM 5
    > 7/19/2006 6:07:22 PM 3
    > 7/19/2006 6:07:29 PM 3
    > 7/19/2006 10:00:00 PM 2
    > 7/19/2006 10:01:00 PM 2
    >
    > This is the expected result of all transactions within one hour of each
    > row, but I had to add column "JOINER" to the table and populate this
    > column with all zeros for something to join every row of the table to
    > itself in the subquery.
    >
    > Does anyone know of a way to do this without the inclusion of the "dummy"
    > column JOINER?
    >
    >
    > --
    > Smartin
     
  4. Smartin

    Smartin
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > In actual fact, you don't need that column. Try:
    >
    > SELECT T1.T, Count(T2.ID) AS Transactions
    > FROM Times T1, Times T2
    > WHERE (ABS(T2.T - T1.T) < (1/24))
    > GROUP BY T1.T
    >


    Thank you, Mr. Steele, for that gem. It works and suit my purpose perfectly.

    As a side note I would like to comment that I analyzed the performance
    of my query and yours and there was no difference from 10 to 5000
    records. As I expected from my own attempt, the relationship between #
    of records and time to return is exponential in both dimensions. What
    surprised me is the how the two queries performed exactly the same.

    Thanks again & Cheers,

    --
    Smartin
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    "Smartin" <smartin108@yahoo.com> wrote in message
    news:q9mdnWBQkbsSRSPZnZ2dnUVZ_qCdnZ2d@giganews.com...
    > Douglas J. Steele wrote:
    > > In actual fact, you don't need that column. Try:
    > >
    > > SELECT T1.T, Count(T2.ID) AS Transactions
    > > FROM Times T1, Times T2
    > > WHERE (ABS(T2.T - T1.T) < (1/24))
    > > GROUP BY T1.T
    > >

    >
    > Thank you, Mr. Steele, for that gem. It works and suit my purpose

    perfectly.
    >
    > As a side note I would like to comment that I analyzed the performance
    > of my query and yours and there was no difference from 10 to 5000
    > records. As I expected from my own attempt, the relationship between #
    > of records and time to return is exponential in both dimensions. What
    > surprised me is the how the two queries performed exactly the same.


    Either way, you're creating a Cartesian Product.

    I don't think you had an index on the Joiner field, did you? Even if you
    did, the usefulness of an index where every single record has the same value
    is questionable.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)
     

Share This Page