CRM Software - SQL used in standard reports to help you create custom reports |
|
|
|
|||
Home Page Site map CRM Software FAQ Import Customers from Spreadsheet Contact Database Share Customer Data between PCs Download FREE CRM Software 30 Day Trial
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)
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 wish to achieve, but we try to develop custom reports that could be useful for the majority of our clients.
Home Page Site map CRM Software FAQ Import Customers from Spreadsheet Contact Database Share Customer Data between PCs Download FREE CRM Software 30 Day Trial
