Counting Transactions Within a Timeframe

[Smartin]

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?

 

[Douglas J. Steele]

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

 

[Smartin]

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,

 

[Douglas J. Steele]

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.