Date/Time Interval - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date/Time Interval

Author

Topic

george909
Starting Member

4 Posts

Posted - 2010-09-15 : 13:09:32

Hello,

I am interested in executing a T-SQL query that allows me to retrieve those accconts who have 3 OR MORE sale transactions back to back, within 60-minutes of one another (time interval). Example: An account can have 4 sale transactions for the same day (2010-08-02), but not necessarly meet the 60-minute time interval criteria, so they should be exclucde. I need to only query those transactions that meet a 3 or more transaction count, all within 60-minutes of one another. Any suggestions?

Example:

ACCOUNT DATE
00001 2010-08-02 22:27:22.967
00001 2010-08-02 22:27:44.897
00001 2010-08-02 22:28:15.477
00001 2010-08-10 11:34:57.020
00002 2010-08-31 14:58:33.277
00002 2010-08-31 20:11:25.633

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-15 : 14:39:18

here is one way. someone else may have something better.

With sales (ACCOUNT, DATE)
AS (SELECT ACCOUNT, DATE FROM YourTable)

SELECT	distinct s1.*
FROM	sales s1
JOIN	sales s2
On	s1.ACCOUNT = s2.ACCOUNT
And	DateDiff(minute, s1.DATE, s2.DATE) < 61
And	s1.DATE < s2.DATE
JOIN	sales s3
On	s1.ACCOUNT = s3.ACCOUNT
And	DateDiff(minute, s1.DATE, s3.DATE) < 61
And	s1.DATE < s3.DATE
And	s2.DATE < s3.DATE

george909
Starting Member

4 Posts

Posted - 2010-09-15 : 15:25:31

Thanks Russell, Looks like this approach may work. Any thoughts on how I can further add an aggregate to this query? Example, 3 sale transactions or greater, within 60-minutes of each other, and the sum of the sales >= $300.

Table: MyTable

ACCOUNT-------------DATE-----------------------------TranAmt
00001---------------2010-08-02 22:27:22.967----------$100.00
00001---------------2010-08-02 22:37:44.897----------$100.00
00001---------------2010-08-02 22:58:15.477----------$100.00
00001---------------2010-08-10 11:34:57.020-----------$14.99
00002---------------2010-08-31 14:58:33.277-----------$50.00
00002---------------2010-08-31 20:11:25.633-----------$40.00

expected results:

ACCOUNT-------------DATE-----------------------------TranAmt
00001---------------2010-08-02 22:27:22.967----------$100.00
00001---------------2010-08-02 22:37:44.897----------$100.00
00001---------------2010-08-02 22:58:15.477----------$100.00

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources