How many transactions in a day? 1

[asimeqi]

I have a table like this:

Declare @transactions Table(tran_id Integer, account_id Integer, tran_date datetime)

Insert Into @transactions Values(1, 1, '2006-06-08 08:00:00')
Insert Into @transactions Values(2, 1, '2006-06-08 14:00:00')
Insert Into @transactions Values(3, 2, '2006-06-09 08:00:00')
Insert Into @transactions Values(4, 1, '2006-06-09 08:00:00')
Insert Into @transactions Values(5, 1, '2006-06-10 08:00:00')
Insert Into @transactions Values(6, 1, '2006-06-10 15:00:00')
Insert Into @transactions Values(7, 3, '2006-06-10 09:00:00')
Insert Into @transactions Values(8, 3, '2006-06-10 12:00:00')

I need to write a query that returns the accounts that have more than one transaction per day and the number of days on which the threshold is passed.

So in the above case I would get:

account_id       num_days
1                  2
3                  1

Any ideas?

 

[bborissov]

SELECT Account_Id, Cnt AS num_days
       FROM (SELECT Account_Id, tran_date, COUNT(*) AS Cnt
                    FROM MyTable
                    GROUP BY Account_Id, tran_date) MyTbl
WHERE Cnt > 1

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[gmmastros]

This...

Select Account_Id,
       Count(1) As Num_days
From   (
       Select Account_Id,
              DATEADD(dd, DATEDIFF(dd, 0, tran_date), 0) As TheDate,
              Count(1) As TheCount
       From   @Transactions
       Group By Account_Id, 
              DATEADD(dd, DATEDIFF(dd, 0, tran_date), 0) 
       Having Count(1) > 1
       ) As A
Group By Account_Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

borislav,

That's close, but since there is a time component involved, you can't simply group by the date. I used vongrunt's trick from his date faq's to 'zero out the time'. Then you can use it to group by.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Yes, but I saw only date part All time portions looked like 00:00:00 to me when I wrote the post :-(
If I spend some time to copy and paste the data and test my example ...
Just have no time now :-(

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[asimeqi]

Thanks George. That is an wonderful trick.