T-SQL for number of hits - 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)

T-SQL for number of hits

Author

Topic

george909
Starting Member

4 Posts

Posted - 2010-11-12 : 17:37:25

I need some assistance from the experts.

I am trying to write myself a T-SQL query for SQL server 2005 that looks at transactions and will output the number accounts that meet the criteria below.

Example. I have a table called DETAIL. it stores all the transactions for all accounts that are part of my business. it includes dollars, responses, transaction codes and date/time the transaction occurred. It is an e-commerce database.

What I want to be able to do is pull stats on the # of my customers (accounts) that submit multiple purchases within a given period. For example: How many customers in a give month (September-2010), submitted 3 or more purchases within 60 minutes of each other (of the 3 or more transactions considered). I can have multiple customers that meet this criteria (100+).

DETAIL TABLE:
ACCT DATE RESP TRAN_AMT
001 2010-09-13 06:49:21.667 APPROVED 125.67
001 2010-09-15 07:01:40.257 APPROVED 327.07
001 2010-09-17 07:03:32.530 APPROVED 49.50

In this example, I should get 1 customer(acct). However I have 11,000 transactions I need to apply this query on.

Also what if I wanted 2 or more transactions within 60 minutes of each other, with an exsisting previous transaction prior to the 2 or more transactions with a response = DECLINED. EXAMPLE below.

DETAIL TABLE:
ACCT DATE RESP TRAN_AMT
001 2010-09-13 06:49:21.667 DECLINED 125.67
001 2010-09-15 07:01:40.257 APPROVED 327.07
001 2010-09-17 07:03:32.530 APPROVED 49.50

This table meets the criteria therefore 1 customer (account) should be the results.

your assistance is appreciated.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-14 : 01:39:43

select t.* from table as t
where not exists(select * from table as x where x.date between dateadd(hour, -1, s.date) and s.date and x.resp = 'declined')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources