Compare current month totals with last months totals

  • Thread starter Thread starter Ixtreme
  • Start date Start date
I

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.
 
Ixtreme said:
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..
Click to expand...

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.
 
Back
Top