SQL - Count number of transactions if at least one of the transactions is today

Question

I have a database of transactions made by customers such that each transaction has a specific date. I need to count the number of transactions made by each customer in the last two months ONLY if there was a transaction made by the customer today.

I have been thinking that it requires me to use WHERE to set the complete range of two months and another HAVING statement to make sure the newest date (MAX of that customers transactions) is equal to today's date but I cannot seem to get it to work. Does this sound like the correct way to be going about this problem or is there a better way?

Thank you!

Maybe, and maybe.... To get better answer you would have to add more info about the your tables and queries that you tried but they didn't work — user3012759, Jul 21 '17 at 15:08

score 3 · Answer 1 · answered Jul 24 '17 at 14:41

You don't provide any information about how your schema is, but I assume you have a Customer table and a Transaction table. Consider this example with 4 customers and 12 transactions.

Customers

| id |     name |
|----|----------|
|  1 |   Google |
|  2 | Facebook |
|  3 |    Hooli |
|  4 |   Yahoo! |

Transactions

| id | transaction_date | customer_id |
|----|------------------|-------------|
|  1 |       2017-04-15 |           1 |
|  2 |       2017-06-24 |           1 |
|  3 |       2017-07-09 |           1 |
|  4 |       2017-07-24 |           1 |
|  5 |       2017-07-23 |           2 |
|  6 |       2017-07-22 |           2 |
|  7 |       2017-07-21 |           2 |
|  8 |       2017-07-24 |           2 |
|  9 |       2017-07-24 |           3 |
| 10 |       2017-07-23 |           4 |
| 11 |       2017-07-22 |           4 |
| 12 |       2017-07-21 |           4 |

To count the number of transactions the last two months by each customer a simple group by will do the job:

select name, count(*) as number_of_transactions
from transactions t
  inner join customers c on c.id = t.customer_id
where t.transaction_date > dateadd(month, -2, getdate())
group by c.name

This yields

|     name | number_of_transactions |
|----------|------------------------|
| Facebook |                      4 |
|   Google |                      3 |
|    Hooli |                      1 |
|   Yahoo! |                      3 |

To retrieve only customers that have a transaction with a transaction_date equal to today we can use an exists to check if such a row exist.

select name, count(*) as number_of_transactions
from transactions t
  inner join customers c on c.id = t.customer_id
where t.transaction_date > dateadd(month, -2, getdate())
  and exists(select *
             from transactions
             where customer_id = t.customer_id
               and transaction_date = convert(date, getdate()))
group by c.name

So, if a row in the transaction table that has a transaction_date equal to today and the customer_id is equal to the customer_id from the main query include it in the result. Running that query (given that 24th July is today) gives us this result:

|     name | number_of_transactions |
|----------|------------------------|
| Facebook |                      4 |
|   Google |                      3 |
|    Hooli |                      1 |

Check out this sql fiddle http://sqlfiddle.com/#!6/710c94/13

score 0 · Answer 2 · answered Jul 21 '17 at 15:09

You can toss a subquery in your WHERE clause to find customers that have had sales today:

SELECT count(*) /*count of transactions*/
FROM transactions
WHERE 
    /*Transactions in the last two months*/
    transaction_date > DATEADD(mm, -2, GETDATE())
    /*For customers that have had a sale today*/
    customer_number in (SELECT customer_number FROM transactions WHERE transaction_date = GETDATE());

Totally guessing at your table structure, table name, and field names, but this should get you close.

score 0 · Answer 3 · answered Jul 21 '17 at 15:23

Alternatively, you can try to do a inner join:

SELECT t2.CustomerID,count(*) as TransactionsCount 
FROM [Tansaction] t1 INNER JOIN [Tansaction] t2
ON t1.CustomerID= t2.CustomerID
WHERE CONVERT(date,t1.TransactionDateTime) = CONVERT(date,GETDATE())
AND t2.TransactionDateTime>= DATEADD(mm, -2, GETDATE())
GROUP BY  t2.CustomerID

score 0 · Answer 4 · answered Jul 21 '17 at 15:39

First, you would need to get the list of customers that had made a transaction today. I'm assuming you have a 'transactiontable' that contains transaction dates and customer details.

Do a select from this transactiontable using the following method:

Select count of distinct(transactiondate), Customer 
from Transactiontable
where transactiondate > dateadd(months,-2, getdate())
and customer in (select customer from transactiontable 
`where cast(transactiondate as date) = cast(getdate() as date))

SQL - Count number of transactions if at least one of the transactions is today

4 Answers4