Question For Customer Transaction BAQ

Kinetic ERP
1

Hello!

Quick question, I’m trying to get a query thrown together that will show all customers that have not had an invoice posted in the last 7 years for deactivation purposes. What is the best way to go about creating this?

Thanks!

2

This is certainly not perfect, but it should get you closer:

select 
	[AllCustomers].[Customer_CustID] as [Customer_CustID],
	[AllCustomers].[Calculated_LastClosedDate] as [Calculated_LastClosedDate]
from  (select 
	[Customer].[CustID] as [Customer_CustID],
	(max(InvcHead.ClosedDate)) as [Calculated_LastClosedDate]
from Erp.InvcHead as InvcHead
inner join Erp.Customer as Customer on 
	Customer.Company = InvcHead.Company
	and Customer.CustNum = InvcHead.CustNum
group by [Customer].[CustID])  as AllCustomers
where (AllCustomers.Calculated_LastClosedDate <= dateadd (year, -7, Constants.Today))

Basically, it is a subquery that links Customer to InvcHead. The Sub only returns the last (max) closed date for the invoice. Then the top level shows you any customers that the last closed data is 7 years or older.

3

Thank you!