Compare current month totals with last months totals

[Ixtreme]

I have a database with transactions for each customer (based on
unique
Client Identifier) with a Data Dump Date field to represent the
transaction date.
For each Client Identifier I would like to sum the field Base Amount
for the
current month and compare that amount with the previous month totals
for that customer. If the current month total is greater than the
total average monthly amount for the customer, I want all the
individual
transactions for that customer for the current month to be displayed.

 

[Bob Barrows]

I have a database with transactions for each customer (based on
unique
Client Identifier) with a Data Dump Date field to represent the
transaction date.
For each Client Identifier I would like to sum the field Base Amount
for the
current month and compare that amount with the previous month totals
for that customer. If the current month total is greater than the
total average monthly amount for the customer, I want all the
individual
transactions for that customer for the current month to be displayed..

The technique is called "correlated subquery". It could all be done in a
single query, but in order to simplify things and make it a little easier to
debug, I'm going to suggest the creation of the following saved queries:

MonthlyTotalsCurrentMonth:
select Cust, sum(BaseAmt) as MthlyTotal
from tablename
where datefield >= dateserial(year(date()),Month(Date()),1)
and datefield < dateserial(year(date()),Month(Date())+1,1)
group by Cust

MonthlyTotalsPriorMonths:
select Cust,Year(DateField),Month(Datefield), sum(BaseAmt) as MthlyTotal
from tablename
where datefield < dateserial(year(date()),Month(Date()),1)
group by Cust,Year(DateField),Month(Datefield)

AvgMthlyTotalsPriorMonths:
select cust, Avg(MthlyTotal) as AvgTotalsPriorMths
FROM MonthlyTotalsPriorMonths
GROUP BY Cust

CustsWithHighTotalsCurrentMonth:
select Cust From MonthlytotalsCurrentMonth as c join
AvgMthlyTotalsPriorMonths as p
on c.Cust = p.cust
WHERE c.MthlyTotal>p.AvgTotalsPriorMths

Now we can use the following query to get the transactions for each customer
with high totals:
Select Cust,BaseAmt
FROM tablename t join CustsWithHighTotalsCurrentMonth as p
ON t.Cust=p.Cust

Substitute the actual table and field names in the above and test them one
at a time.