Custom Reports - SQL used in standard reports to help you create custom reports

CRM Software Custom Reports

Download CRM Software Free 30 Day Trial
Download CRM Software Free 30 Day Trial

CRM Testimonials

Amphis Customer CRM Software

Amphis Customer CRM Software makes it easy to manage your Contacts, create Quotes and Invoices, manage your Jobs and Appointments, Mail Merge and so much more. You can download a free trial and see for yourself. There are no monthly or yearly fees, just a one-off payment. See prices

We have some short videos about Amphis Customer CRM Software.

CRM Software - SQL used in standard reports to help you create custom reports

In Amphis Customer CRM you can create Custom Reports to suit your business requirements. This is some of the SQL statements used to create the standard reports which may be useful to you if you want to create your own custom reports.

You can create a custom report using the Custom Report Builder (from the Reports->Custom Report Builder menu)

For more information on how to create a custom report, see Create CRM Custom Reports

Here is the list of SQL commands used in the standard reports available from the Reports menu.


customers by group

select g.groupname as [Group], displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name] from customer c, groupcustomer gc, [group] g where c.customerid = gc.customerid and g.id = gc.groupid UNION select '' as [Group], displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name] from customer c where not exists (select * from groupcustomer gc where c.customerid = gc.customerid ) order by 1


customers by status

select s.lookupvalue as [Customer Status], displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName from customer c, lookup_customerstatus s where c.customerstatusid = s.lookupkey UNION select '' as [Customer Status], displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name] from customer c, lookup_customerstatus s where customerstatusid in (0,-1) order by 1


customers with outstanding invoices

SELECT customerid, displayid, companyname, lastname, firstname, titleid, [position], customerstatusid, [balance], amountspent FROM customer WHERE balance < 0


overdue accounts

SELECT DISTINCT c.customerid, displayid, companyname, lastname, firstname, titleid, [position], customerstatusid, [balance], amountspent FROM customer c INNER JOIN history h ON c.customerid = h.customerid WHERE h.status = 'Overdue'


top spenders

SELECT top 10 customerid, displayid, companyname, lastname, firstname, titleid, [position], customerstatusid, [balance], amountspent FROM customer WHERE amountspent > 0 ORDER BY amountspent DESC


customers who have spent more than 1000

SELECT customerid, displayid, companyname, lastname, firstname, titleid, [position], customerstatusid, [balance], amountspent FROM customer WHERE amountspent > 1000 ORDER BY amountspent DESC


customers with specific status

SELECT customerid, displayid, companyname, lastname, firstname, titleid, [position], customerstatusid, [balance], amountspent FROM customer WHERE customerstatusid = 1


invoices created in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], documentnumber as [Invoice Id], displaydate as [Created Date], descr as Description,Total,Status, FileName from customer c inner join history h on c.customerid=h.customerid where what='invoice' AND h.displaydate > DATE()-30 ORDER BY displaydate


quotes created in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], DocumentNumber as [Quote Id], displaydate as [Created Date], descr as Description, Total,filename from customer c inner join history h on c.customerid=h.customerid where what='quote' AND h.displaydate > DATE()-30 ORDER BY displaydate


receipts created in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], displaydate as [Date], descr as Description, h.total as Amount, it.invoiceid as [Invoice], filename from customer c, history h, invoice_transaction it where c.customerid=h.customerid AND h.what = 'receipt' AND c.customerid=it.customerid AND h.documentnumber=it.documentnumber AND h.displaydate > DATE()-30 ORDER BY displaydate


refunds created in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], displaydate as [Date], descr as Description, h.total as Amount, filename from customer c inner join history h on c.customerid=h.customerid where what = 'refund' AND h.displaydate > DATE()-30 ORDER BY displaydate


transactions created in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], documentnumber as [Transaction Id], displaydate as [Created Date], what as [Type], descr as Description,Total,Status, filename from customer c inner join history h on c.customerid=h.customerid where what IN ('invoice','receipt','refund') AND h.displaydate > DATE()-30 ORDER BY displaydate


customer telephone numbers

select displayid as CustomerId, CompanyName, ct.lookupvalue as [Title], LastName as [Last Name],FirstName as [First Name], worktel as [Work Tel], hometel as [Home Tel], mobiletelwork as [Work Mobile], mobiletelpersonal as [Personal Mobile] from customer c INNER JOIN lookup_contacttitle ct on c.titleId = ct.lookupkey


all customer notes

SELECT displayId as [CustomerId], Companyname as [Company Name], Lastname as [Last Name], Firstname as [First Name] , descr as [Notes],[Date] FROM customer c, history h where c.customerid = h.customerid and h.what='notes' order by 1,2,3,6 desc


appointments in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], [apptstarttime] as [Appointment Time], apptsubject as Subject, apptdesc as Description, apptlocation as [Location] from customer c inner join appointment a on c.customerid=a.customerid WHERE apptstarttime > DATE()-30 AND apptstarttime < DATE()+1 UNION select '' as CustomerId, '', '' as [Last Name], '' as [First Name], [apptstarttime] as [Appointment Time], apptsubject as Subject, apptdesc as Description, apptlocation as [Location] from appointment a WHERE customerid = 0 AND apptstarttime > DATE()-30 AND apptstarttime < DATE()+1 ORDER BY 5


site visits in the last 30 days

select displayid as CustomerId, CompanyName, LastName as [Last Name],FirstName as [First Name], displaydate as [Visit Date], descr as Description from customer c inner join history h on c.customerid=h.customerid where what IN ('site visit') AND h.displaydate > DATE()-30 ORDER BY displaydate


appointments due in the next 30 days

select c.displayid as CustomerId, c.CompanyName, c.LastName as [Last Name],c.FirstName as [First Name], apptstarttime as [Start Time], apptsubject as [Subject], apptdesc as [Description], apptlocation as [Location] from customer c inner join appointment a on c.customerid=a.customerid WHERE a.apptstarttime < DATE()+30 AND a.apptstarttime > DATE()-1 UNION select '' as CustomerId, '' , '' as [Last Name],'' as [First Name], apptstarttime as [Start Time], apptsubject as [Subject], apptdesc as [Description], apptlocation as [Location] from appointment a WHERE a.customerid=0 AND a.apptstarttime < DATE()+30 AND a.apptstarttime > DATE()-1 ORDER BY 5


tasks due in the next 30 days

select c.displayid as CustomerId, c.CompanyName, c.LastName as [Last Name],c.FirstName as [First Name], taskstarttime as [Start Time],taskduetime as [Due Time], tasksubject as [Subject], taskdesc as [Description] from customer c inner join task a on c.customerid=a.customerid WHERE a.taskduetime < DATE()+30 UNION select '' as CustomerId, '', '' as [Last Name],'' as [First Name], taskstarttime as [Start Time],taskduetime as [Due Time], tasksubject as [Subject], taskdesc as [Description] from task a WHERE a.customerid=0 AND a.taskduetime < DATE()+30 ORDER BY 6


(We welcome your suggestions for CRM Custom Reports we could create (for free). To make a suggestion, please click here)

For more information on how to create a custom report, and to see more examples of custom reports, see Create Custom Reports

Disclaimer: we develop custom reports at no charge, often at the request of clients. We cannot guarantee that these custom reports will produce exactly the results you want to achieve, but we try to develop custom reports that could be useful for the majority of our clients.



Download CRM Software Free 30 Day Trial
Download CRM Software Free 30 Day Trial

CRM Case Studies

CRM Case Studies