Trying to group transactions by customer and variable date range

Transact-SQL
1

I have a view named transactions_per_day

I am trying to get SQL to return when 2 or more transactions for the customer add up to over 10,000 for a variable time period.

I've set the @startdate and @enddate variables so that my time period could be a week, two weeks, or a month.

I've also set the @maxamount variable to 10000.

I was able to get the sums of all the transactions grouped by customer_id, and apply an IF statement to test is the sum of the amounts are greater than the @maxamount variable, but I don't know how to incorporate the date range variables in the SQL query.

together
together1667×713 143 KB

Ideally, I would like to only see the customers and transactions that have summed to surpassed the @maxamount threshold during the variable time range set by @startdate and @enddate.

customer_id | Sum of transactions during variable period | number of days during variable period

Thanks for any help, I appreciate it.

2

That doesn't look like SQL Server syntax - I am guessing you are using MySQL which is a completely different product.

With that said - you are looking for the HAVING clause.

SELECT ...
  FROM ...
 WHERE ...
 GROUP BY ...
HAVING SUM(transaction_total_per_day) > 10000
1 Like
3

Thank you.

With your help I was able to get the following:

soultion